文藝復興

Viewing posts for the category Powershell

Task Scheduler設定排程錯誤時發送Mail - Powershell

Posted by: bart30508 | in Powershell | 4 days, 11 hours ago |

發送排程錯誤mail腳本

首先是要執行的腳本,依照需求改成你要的:

Upload RDL Report to Power BI Server with Powershell - PowerBI

Posted by: bart30508 | in PowerBI, Powershell | 2 months, 1 week ago |

Script like below:

Export all of Report on Power Bi Server - Power Bi

Posted by: bart30508 | in PowerBI, Powershell | 2 months, 1 week ago |

<# .SYNOPSIS Export of all SSRS reports datasources and images .DESCRIPTION This PowerShell script exports all (or filtered) reports, data sources and images directly from the ReportServer database to a specified folder. For the file name the complete report path is used; for file name invalid characters are replaced with a -. Reports are exported with .rdl as extension, data sources with .rds and resources without any additional extension. Please change the "Configuration data" below to your enviroment. Works with SQL Server 2005 and higher versions in all editions. Requires SELECT permission on the ReportServer database. .NOTES Author : Olaf Helper Requires: PowerShell Version 1.0, Ado.Net assembly .LINK GetSqlBinary: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getsqlbinary.aspx #>

Power Bi Cloud And On-Premise Upload Report Through PowerShell - PowerBi

Posted by: bart30508 | in PowerBI, Powershell | 2 months, 1 week ago |

PowerBi On-Premise Upload

<# https://github.com/microsoft/ReportingServicesTools
Invoke-Expression (Invoke-WebRequest https://raw.githubusercontent.com/Microsoft/ReportingServicesTools/master/Install.ps1)

https://www.powershellgallery.com/packages/PublishPBIXFile/1.0.0.1/Content/PublishPBIXFile.ps1 #>

# Code By SB 2019
$ReportServerURI = 'http://localhost:8080/Reports'          # Input Local path of powerbi file
$filePath = "C:\Users\Export_PBI_SSRS\Inventory.pbix"       # Input Local path of powerbi file
$PBIxfileName = "Inventory"                                 # INput your Powerbi File Name
$FolderName ='TestingByMax'                                 # Input PowerBI server Folder Name Where you wann to deploy
$Username ='Username'
$password ='Password'                          
$ReportServerName ='DBServerName'                           #input SQL server where POWERBI database installed
$ReportServerDatabase = 'ReportServer'                      #input PowerBi Database Name

$ConnectionString ='data source=Client01\SQL2019;initial catalog=Client_SB_1'  # input New Connection String / Client ConnectionString

$FolderLocation = '/'
$FolderPath = $FolderLocation + $FolderName

write-host "Deployment Started ..." -ForeGroundColor Yellow 
$session = New-RsRestSession -ReportPortalUri $ReportServerURI
Write-RsRestCatalogItem -WebSession $session -Path $filePath -RsFolder $folderPath -Description $Description -Overwrite
$datasources = Get-RsRestItemDataSource -WebSession $session -RsItem "$FolderPath/$PBIxfileName"
$dataSources[0].DataModelDataSource.AuthType = ‘Windows'
$dataSources[0].DataModelDataSource.Username = $ConnectionString 
$dataSources[0].DataModelDataSource.Secret = $password

Set-RsRestItemDataSource -WebSession $session -RsItem "$folderPath/$PBIxfileName" -RsItemType PowerBIReport -DataSources $datasources

$ID =  $dataSources[0].Id

#$Query = " Update [DataModelDataSource] SET ConnectionString = Username From [dbo].[DataModelDataSource] Where DataSourceID ='" + $ID  + "' "
#Invoke-Sqlcmd -Query $Query -ServerInstance CPMSUNRSQL17\CPMSRINST17 -Database ReportServerPowerBI

$datasources = Get-RsRestItemDataSource -WebSession $session -RsItem "$FolderPath/$PBIxfileName"
$dataSources[0].DataModelDataSource.Username = $Username
$dataSources[0].DataModelDataSource.Secret = $password
Set-RsRestItemDataSource -WebSession $session -RsItem "$folderPath/$PBIxfileName" -RsItemType PowerBIReport -DataSources $datasources

write-host "Deployment Done . . ." -ForeGroundColor Green

Upload a whole Folder

<# https://github.com/microsoft/ReportingServicesTools
Invoke-Expression (Invoke-WebRequest https://raw.githubusercontent.com/Microsoft/ReportingServicesTools/master/Install.ps1)

https://www.powershellgallery.com/packages/PublishPBIXFile/1.0.0.1/Content/PublishPBIXFile.ps1 #>

$SourceDirectory = 'C:\Users\Export_PBI_SSRS'
# Code By SB 2019
$ReportServerURI = 'http://localhost/Reports' # Input Local path of powerbi file
#$filePath = "C:\Users\chenc89\Desktop\jobdata\Develop\Powerbi_Owner\Export_PBI_SSRS\Inventory.pbix"     # Input Local path of powerbi file
$FolderName ='TestingByMax/GReports'       # Input PowerBI server Folder Name Where you wann to deploy
$Username ='UserName'
$password ='Password'                          
$ReportServerName ='PBIDBServeName'                #input SQL server where POWERBI database installed
$ReportServerDatabase = 'ReportDBName'           #input PowerBi Database Name 
$ConnectionString ='data source=Client01\SQL2019;initial catalog=Client_SB_1'  # input New Connection String / Client ConnectionString
$FolderLocation = '/'
$FolderPath = $FolderLocation + $FolderName


foreach($filePath in Get-ChildItem $SourceDirectory -Filter *.pbix)
{
$PBIxfileName = $filePath -replace '.pbix','' # INput your Powerbi File Name
Write-host ""

write-host $PBIxfileName -ForegroundColor Green 
try{
    write-host "Deployment Started ..." -ForeGroundColor Yellow 
    $session = New-RsRestSession -ReportPortalUri $ReportServerURI
    Write-RsRestCatalogItem -WebSession $session -Path $filePath -RsFolder $folderPath -Description $Description -Overwrite
    $datasources = Get-RsRestItemDataSource -WebSession $session -RsItem "$FolderPath/$PBIxfileName"
    $dataSources[0].DataModelDataSource.AuthType = ‘Windows'
    $dataSources[0].DataModelDataSource.Username = $ConnectionString 
    $dataSources[0].DataModelDataSource.Secret = $password

    Set-RsRestItemDataSource -WebSession $session -RsItem "$folderPath/$PBIxfileName" -RsItemType PowerBIReport -DataSources $datasources

    $ID =  $dataSources[0].Id

    #$Query = " Update [DataModelDataSource] SET ConnectionString = Username From [dbo].[DataModelDataSource] Where DataSourceID ='" + $ID  + "' "
    #Invoke-Sqlcmd -Query $Query -ServerInstance CPMSUNRSQL17\CPMSRINST17 -Database ReportServerPowerBI

    $datasources = Get-RsRestItemDataSource -WebSession $session -RsItem "$FolderPath/$PBIxfileName"
    $dataSources[0].DataModelDataSource.Username = $Username
    $dataSources[0].DataModelDataSource.Secret = $password
    Set-RsRestItemDataSource -WebSession $session -RsItem "$folderPath/$PBIxfileName" -RsItemType PowerBIReport -DataSources $datasources

    write-host "Deployment Done . . ." -ForeGroundColor Green 
}catch [System.IO.IOException] {
     $msg = "Error while reading rdl file : '{0}', Message: '{1}'" -f $rdlFile, $_.Exception.Message
     Write-Error msg
}catch [System.Web.Services.Protocols.SoapException]{
 if ($_.Exception.Detail.InnerText -match "[^rsItemAlreadyExists400]")
            {
                Write-Error "Report: $reportName already exists." 
            }
            else
            {
                $msg = "Error uploading report: $reportName. Msg: '{0}'" -f $_.Exception.Detail.InnerText
                Write-Error $msg
            }
}

}

Power Bi Clouid Upload

import requests
from requests_toolbelt.multipart.encoder import MultipartEncoder

groupId = "your workspace ID"
reportName = "reportName that you choose to appear on the server"
accessToken = "xx"

url = 'https://api.powerbi.com/v1.0/myorg/groups/' + groupId + '/imports?datasetDisplayName=' + reportName
headers = {
    'Content-Type': 'multipart/form-data',
    'authorization': 'Bearer ' + accessToken
}
file_location = '/xx.pbix'
# you need this dictionary to convert a binary file into form-data format
# None here means we skip the filename and file content is important 
files = {'value': (None, open(file_location, 'rb'), 'multipart/form-data')}

mp_encoder = MultipartEncoder(fields=files)

r = requests.post(
    url=url,
    data=mp_encoder,  # The MultipartEncoder is posted as data, don't use files=...!
    # The MultipartEncoder provides the content-type header with the boundary:
    headers=headers
)

PublishPBIXFile.ps1

(Install不了可以直接執行)

 or 

Subscribe

* indicates required

Recent Posts

Archive

2022
2021

Categories

Apache 1

Data Science 2

Dbfit 1

Design Pattern 1

Devops 3

DigitalOcean 1

Django 1

English 3

Excel 5

Flask 3

Git 1

HackMD 1

Heroku 1

Html/Css 1

Linux 4

Machine Learning 2

Manufacture 1

Mezzanine 18

Oracle 1

Postgresql 7

PowerBI 4

Powershell 4

Python 21

SEO 2

SQL Server 51

SQLite 1

Windows 1

database 8

work-experience 1

其他 1

自我成長 1

資料工程 1

Tags

SEO(1) Github(2) Title Tag(2) ML(1) 李宏毅(1) SQL Server(18) Tempdb(1) SSMS(1) Windows(1) 自我成長(2) Excel(1) python Flask(1) python(5) Flask(2)

Authors

bart30508 (146)

Feeds

RSS / Atom

© COPYRIGHT 2011-2022. Max的文藝復興. ALL RIGHT RESERVED.