Hi Henry,
I seem to loose all my indents when I copy the code but hopefully you can read it. I can confirm the parameters are populated as I placed a load of write-output statements in publishPBix script. Discovered that it does not get to set $Password to $secret , so dont know if I'm passing enough parameters !
The code I use to call PublichPBIxFile is:
. "C:\Program Files\WindowsPowerShell\Scripts\publishpbixfile\1.0.0.2\PublishPBIXFile.ps1"
$publishPBIXFileSplat = @{
ReportServerURI = $ReportServerURI
folderLocation = $folderLocation
description = $description
pbixfile = $objectFullPath
folderName = $deployFolderName
AuthenticationType = 'Windows'
ConnectionUserName = $ConnStringUser
Secret = $ConnStringPwd
Verbose = $true
Overwrite = 'Y'
}
Publish-PBIXFile @publishPBIXFileSplat
The code for PublichPBIxFile is:
function Publish-PBIXFile {
[CmdletBinding(DefaultParameterSetName = 'ByUserName', SupportsShouldProcess)]
Param(
[Parameter(Mandatory = $true)]
[string]$FolderName,
[Parameter(Mandatory = $true)]
[string]$ReportServerURI,
[Parameter(Mandatory = $true)]
[string]$FolderLocation,
[Parameter(Mandatory = $true)]
[string]$PBIXFile,
[Parameter(Mandatory = $true)]
[string]$Overwrite,
[Parameter()]
[string]$Description = "Description of Your report Should go here",
[Parameter()]
[ValidateSet('Windows','SQL')]
[string]$AuthenticationType,
[Parameter(ParameterSetName = 'ByUserName')]
[string]$ConnectionUserName,
[Parameter(ParameterSetName = 'ByUserName')]
[string]$Secret,
[Parameter(Mandatory = $true, ParameterSetName = 'ByCred')]
[pscredential]$Credential
)
$FolderPath = $FolderLocation + $FolderName
$PBIXName = $PBIXFile.Split('\')[-1].Replace('.pbix', '')
try {
Write-Verbose "Creating a session to the Report Server $ReportServerURI"
# establish session w/ Report Server
$session = New-RsRestSession -ReportPortalUri $ReportServerURI
Write-Verbose "Created a session to the Report Server $ReportServerURI"
}
catch {
Write-Warning "Failed to create a session to the report server $reportserveruri"
Return
}
# create folder (optional)
try {
if ($PSCmdlet.ShouldProcess("$ReportServerURI", "Creating a folder called $FolderName at $FolderLocation")) {
$Null = New-RsRestFolder -WebSession $session -RsFolder $FolderLocation -FolderName $FolderName -ErrorAction Stop
}
}
catch [System.Exception] {
If ($_.Exception.InnerException.Message -eq 'The remote server returned an error: (409) Conflict.') {
Write-Warning "The folder already exists - moving on"
}
}
catch {
Write-Warning "Failed to create a folder called $FolderName at $FolderLocation report server $ReportServerURI but not because it already exists"
Return
}
# upload copy of PBIX to new folder
# MG - Added IF for Overwrite parameter 15/7/2022
if ($Overwrite -eq 'N') {
try {
if ($PSCmdlet.ShouldProcess("$ReportServerURI", "Uploading the pbix from $PBIXFile to the report server ")) {
Write-RsRestCatalogItem -WebSession $session -Path $PBIXFile -RsFolder $folderPath -Description $Description
}
}
catch [System.Exception] {
Write-Warning "The DataSource already exists - moving on"
}
catch {
Write-Warning "Failed to upload the file $PBIXFile to report server $ReportServerURI"
Return
}
}
else {
try {
if ($PSCmdlet.ShouldProcess("$ReportServerURI", "Uploading the pbix from $PBIXFile to the report server ")) {
Write-RsRestCatalogItem -WebSession $session -Path $PBIXFile -RsFolder $folderPath -Description $Description -overwrite
}
}
catch {
Write-Warning "Failed to upload the file $PBIXFile to report server $ReportServerURI"
Return
}
}
try {
Write-Verbose "Getting the datasources from the pbix file for updating"
# get data source object
$datasources = Get-RsRestItemDataSource -WebSession $session -RsItem "$FolderPath/$PBIXName"
Write-Verbose "Got the datasources for updating"
}
catch {
Write-Warning "Failed to get the datasources"
Return
}
try {
Write-Verbose "Updating Datasource"
foreach ($dataSource in $datasources) {
if ($AuthenticationType -eq 'SQL') {
$dataSource.DataModelDataSource.AuthType = 'UsernamePassword'
}
else{
$dataSource.DataModelDataSource.AuthType = 'Windows'
}
if ($Credential -or $username) {
if ($Credential) {
$UserName = $Credential.UserName
$Password = $Credential.GetNetworkCredential().Password
}
else {
$UserName = $ConnectionUserName
$Password = $Secret
}
$dataSource.CredentialRetrieval = 'Store'
$dataSource.DataModelDataSource.Username = $UserName
$dataSource.DataModelDataSource.Secret = $Password
}
if ($PSCmdlet.ShouldProcess("$ReportServerURI", "Updating the data source for the report $PBIXName")) {
# update data source object on server
Set-RsRestItemDataSource -WebSession $session -RsItem "$folderPath/$PBIXName" -RsItemType PowerBIReport -DataSources $datasource
}
}
}
catch {
Write-Warning "Failed to set the datasource"
Return
}
Write-Verbose "Completed Successfully"
}
