Deploy SSRS reports on a customer's machine with PowerShell

Microsoft decided to let us poor tech staff alone with this...

The issues I've found so far:

• After deletion of shared dataset all mobile reports must be re-created from scratch (a re-mapping of datasources is not supported). This is by design, read this, if you cannot believe it
• Deployment is supported to a reachable server only (via target URL)
• There is currently no support to upload a mobile report other than via SSRS portal
• There is currently no support to upload a branding package other than via SSRS portal

As nobody seems to be able to help - even with a bounty! - I'll state what I've found so far:

The following PowerShellScript is a working stand-alone deployment on a disconnected machine.

Assumptions:

• All SSRS-objects (.rsd, .rdl and .rsmobile) are downloaded and are living in appropriate directories below "DeployDirectory"
  • In my case all shared data set filles are in folder "MyProduct Data"
  • In my case all reports are living within sub-folders "EventLog", "Public Reports" and "Report Audit"
  • It would be possible to automate the folders structure, but in this script this part is hard-coded to my needs.
• The PowerShell-Module ReportingServicesTools is placed within a directory with the same name below "DeployDirectory"
• A shared data set's name is globally unique (this is not mandatory by SSRS)

Good luck with this!

$DeployDirectory=".\MyProduct Reports\"$DbURL="1.2.3.4"$srvURI="http://1.2.3.4/ReportServer"$srvURL="http://1.2.3.4/reports"#Write RS Report folders$rootDir="/MyProduct Reports"

#Import the module and create folders. This might be automated too (out of the directory structure found on disc, but this is up to you...)

Import-Module  .\ReportingServicesToolsNew-RsFolder -ReportServerUri $srvURI -Path "/"      -Name "MyProduct Reports"New-RsFolder -ReportServerUri $srvURI -Path $rootDir -Name "MyProduct Data" New-RsFolder -ReportServerUri $srvURI -Path $rootDir -Name "EventLog" New-RsFolder -ReportServerUri $srvURI -Path $rootDir -Name "Public Reports" New-RsFolder -ReportServerUri $srvURI -Path $rootDir -Name "Report Audit" Write-Host "RS Report folders written"

#Create shared data source

New-RsDataSource -RsFolder ($rootDir + "/MyProduct Data") -Name "MyProduct_DatabaseConnection" -Extension "SQL" -ConnectionString ("Data Source=" +  $DbURL + ";Integrated Security=True;Initial Catalog=master;") -CredentialRetrieval "Integrated"Write-Host "Shared data source created"

#Modify shared data set files: The hardcoded reference to the server's URL must be changed

$allRSDs = Get-ChildItem -Recurse -Path $DeployDirectory | Where-Object -FilterScript {$_.Extension -eq ".rsd"}Write-Host "RSDs fetched"$xml = New-Object System.Xml.XmlDocument$nsMngr = New-Object System.Xml.XmlNamespaceManager($xml.NameTable)$nsMngr.AddNamespace("rd","http://schemas.microsoft.com/SQLServer/reporting/reportdesigner")$allRSDs | % {               $FileName=$_.FullName;               $xml.Load($FileName);               $xml.SelectNodes("//rd:ReportServerUrl",$nsMngr) |                % {$_.InnerText=$srvURI};               $newContent = $xml.InnerXml;               Set-Content -Path $FileName -Value $newContent             }Write-Host "Shared data set files modified"Write-RsFolderContent -Recurse -ReportServerUri $srvURI -Path ($DeployDirectory + "MyProduct Data") -RsFolder ($rootDir + "/MyProduct Data") Write-Host "Shared DataSets created"

#Read all created shared data sets out of the database into a table variable

$con = New-Object System.Data.SqlClient.SqlConnection$con.ConnectionString=("Data Source=" + $DbURL + ";Integrated Security=True;Initial Catalog=master;")$con.Open();Write-Host "connection opened"$cmd = New-Object System.Data.SqlClient.SqlCommand$cmd.Connection = $conWrite-Host "command created"$cmd.CommandText = "SELECT ItemID, [name] AS DataSetName, [Path] AS DataSetPath, LEN([name]) AS SortOrder `                    FROM ReportServer.dbo.[Catalog]`                     WHERE [Type]=8"$adapt = New-Object System.Data.SqlClient.SqlDataAdapter$adapt.SelectCommand = $cmd$ds = New-Object System.Data.DataSet$adapt.Fill($ds)$allDs = New-Object System.Data.DataTable$allDs = $ds.Tables[0]Write-Host "shared datasets fetched into cache"Class shDs {[string]$ItemId=""; [string]$DataSetName=""; [string]$DataSetPath="";}function Get-SharedDataSet([string]$DataSetName){    $retVal = New-Object shDs    $Search = ("'" + $DataSetName + "' LIKE DataSetName + '%'")     $Sort = ("SortOrder DESC")    $dsRow = $allDs.Select($Search,$Sort)[0]    $retVal.ItemID=$dsRow["ItemID"].ToString().Trim()    $retVal.DataSetPath=$dsRow["DataSetPath"].ToString().Trim()    $retVal.DataSetName=$dsRow["DataSetName"].ToString().Trim()    return $retVal}Write-Host "function to fetch referenced shared dataset created"$con.Close()Write-Host "connection closed"

#Modify paginated report files: The newly written shared datasets must be written into the report-XML

$allRDLs = (Get-ChildItem -Recurse -Path $DeployDirectory |                 Where-Object -FilterScript {$_.Extension -eq ".rdl"})$xml = New-Object System.Xml.XmlDocument$nsMngr = New-Object System.Xml.XmlNamespaceManager($xml.NameTable)$nsMngr.AddNamespace("rd","http://schemas.microsoft.com/SQLServer/reporting/reportdesigner")$nsMngr.AddNamespace("ns","http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition")$allRDLs | % {               $FileName=$_.FullName;               $xml.Load($FileName);               $xml.SelectNodes("//rd:ReportServerUrl",$nsMngr) |                   % {$_.InnerText=$srvURI};               $xml.SelectNodes("//ns:SharedDataSetReference",$nsMngr) |                   % {                       $it = ("/" + $_.Innertext);                       $ref=$it.SubString($it.LastIndexOf("/")+1);                       $ds = Get-SharedDataSet($ref);                       $_.InnerText=$ds.DataSetPath                       Write-Host ("DataSetPath: " + $_.InnerText)                    };               $newContent = $xml.InnerXml;               Set-Content -Path $FileName -Value $newContent             }Write-Host "paginated report files modified"Write-RsFolderContent -Recurse -ReportServerUri $srvURI -Path ($DeployDirectory + "EventLog") -RsFolder ($rootDir + "/EventLog") Write-RsFolderContent -Recurse -ReportServerUri $srvURI -Path ($DeployDirectory + "Public Reports") -RsFolder ($rootDir + "/Public Reports") Write-RsFolderContent -Recurse -ReportServerUri $srvURI -Path ($DeployDirectory + "Report Audit") -RsFolder ($rootDir + "/Report Audit")Write-Host "paginated reports created"

#Modify mobile report files: This is more complicated... The files are ZIPs actually. These ZIPs contain several files. The metadata.xml and the sources.xml contain hard-coded references and must be changed

$allMobs = (Get-ChildItem -Recurse -Path $DeployDirectory |                 Where-Object -FilterScript {$_.Extension -eq ".rsmobile"})#Unzip SomeName.rsmobile into SomeName.rsmobile.Unzipped Add-Type -AssemblyName System.IO.Compression.FileSystemAdd-Type -AssemblyName System.Collections$unzippedList = New-Object System.Collections.ArrayListClass zippedMobs {[string]$DirectoryName; [string]$rsMobileName; [string]$FileName;}Get-ChildItem -Recurse $path |     Where-Object -FilterScript {$_.Extension -eq ".rsmobile"} |     % {       $zm = New-Object zippedMobs;       $zm.DirectoryName = ($_.FullName + ".Unzipped");       $zm.rsMobileName=$_.FullName;       $zm.FileName=$_.Name;       $unzippedList.Add($zm);       [System.IO.Compression.ZipFile]::ExtractToDirectory($zm.rsMobileName,$zm.DirectoryName)      }Write-Host "Mobile Reports: Files unzipped"

#Open all metadata.xml files in all unzipped folders and modify them

$xml = New-Object System.Xml.XmlDocument$nsMngr = New-Object System.Xml.XmlNamespaceManager($xml.NameTable)$nsMngr.AddNamespace("ns","http://schemas.microsoft.com/sqlserver/reporting/2016/02/mobilereportpackage")$nsMngr.AddNamespace("mrp","http://schemas.microsoft.com/sqlserver/reporting/2016/02/mobilereportpublisher")$unzippedList | % {         $FileName=($_.DirectoryName + "\metadata.xml");         $xml.Load($FileName);         $xml.SelectNodes("//ns:dataSet",$nsMngr) |             % {                $ref=$_.Attributes["name"].Value;                $ds = Get-SharedDataSet($ref);                $_.Attributes["mrp:Server"].Value=$srvURL;                $_["id"].InnerText=$ds.ItemID;                $_["path"].InnerText=$ds.DataSetPath              };         $newContent = $xml.InnerXml;         Set-Content -Path $FileName -Value $newContent        }Write-Host "Mobile Reports: All metadata.xml re-mapped"

#Open all sources.xml files in all unzipped folders and modify them

$xml = New-Object System.Xml.XmlDocument$unzippedList | % {         $FileName=($_.DirectoryName + "\sources.xml");         $xml.Load($FileName);         $xml.SelectNodes("//Shared") |             % {              $ref=$_.Attributes["Name"].Value;             $ds = Get-SharedDataSet($ref);             $_.Attributes["ServerUri"].Value=$srvURL;              $_.Attributes["Guid"].Value=$ds.ItemID;             $_.Attributes["DataItemLocation"].Value=$ds.DataSetPath            };         $newContent = $xml.InnerXml;          Set-Content -Path $FileName -Value $newContent        }Write-Host "Mobile Reports: All sources.xml re-mapped"

#Rename all original .rsmobile files

$unzippedList | % {Rename-Item -Path $_.rsMobileName -NewName ($_.FileName + ".old")}Write-Host "Mobile Reports: Renamed all orginal .rsmobile files"#Create new ZIP file for all mobile reports$unzippedList | % {                   [System.IO.Compression.ZipFile]::CreateFromDirectory($_.DirectoryName,$_.rsMobileName,[System.IO.Compression.CompressionLevel]::NoCompression, $FALSE)                  }Write-Host "Re-created all mobile report files"

Attention

Allthough the created ZIP-files (the new .rsmobile files) are valid and contain the correct content, it is impossible to upload them via SSRS portal (error: invalid report package). But - funny enough! - when you use explorer's send to compressed directory and rename the resulting ZIP file accordingly, this can be uploaded.

Still open questions:

• How can one create the .rsmobile (ZIP-file) that it is uploadable? (must be the same way of zipping as windows does it implicitly when sending to a compressed folder)
• How can one upload a mobile report programmatically?

This one I could answer in the meanwhile (see second answer):

• How can one upload a branding package programmatically?

Upload of a branding package

With this I finally managed to upload a branding package

This stored procedure will read and write all Catalog entries:

CREATE PROCEDURE InstallBrandingPackage(    --Arguments     @BrandingZIPPath VARCHAR(300)    ,@BrandingColorsPath VARCHAR(300)    ,@BrandingLogopath VARCHAR(300)    ,@BrandingType VARCHAR(100)    ,@BrandingName VARCHAR(100)    ,@BrandingVersion VARCHAR(10))ASBEGIN    BEGIN TRANSACTION;    SET XACT_ABORT ON;    DECLARE @BrandingZIPName VARCHAR(150)=RIGHT(@BrandingZIPPath,CHARINDEX('\',REVERSE(@BrandingZIPPath))-1);    DECLARE @BrandingZIP VARBINARY(MAX);    DECLARE @BrandingColorsJSON VARBINARY(MAX);    DECLARE @BrandingLogoPNG VARBINARY(MAX);    CREATE TABLE #tmpBranding(FileType VARCHAR(100),Content VARBINARY(MAX));    DECLARE @cmd VARCHAR(MAX);    SET @cmd=    'INSERT INTO #tmpBranding(FileType,Content)        SELECT ''zip'',BulkColumn FROM OPENROWSET(BULK ''' + @BrandingZIPPath + ''', SINGLE_BLOB) AS x';    EXEC(@cmd);    SET @cmd=    'INSERT INTO #tmpBranding(FileType,Content)        SELECT ''colors'',BulkColumn FROM OPENROWSET(BULK ''' + @BrandingColorsPath + ''', SINGLE_BLOB) AS x';    EXEC(@cmd);    SET @cmd=    'INSERT INTO #tmpBranding(FileType,Content)        SELECT ''logo'',BulkColumn FROM OPENROWSET(BULK ''' + @BrandingLogopath + ''', SINGLE_BLOB) AS x';    EXEC(@cmd);    SET @BrandingZIP=(SELECT Content FROM #tmpBranding WHERE FileType='zip');    SET @BrandingColorsJSON=(SELECT Content FROM #tmpBranding WHERE FileType='colors');    SET @BrandingLogoPNG=(SELECT Content FROM #tmpBranding WHERE FileType='logo');    --needed variables and IDs taken from System Resources    DECLARE @SystemResourceID UNIQUEIDENTIFIER           ,@SystemResourcePath VARCHAR(500)           ,@PolicyID UNIQUEIDENTIFIER           ,@UserID UNIQUEIDENTIFIER;    SELECT TOP 1 @SystemResourceID=ItemID                ,@SystemResourcePath=[Path]                ,@PolicyID=PolicyID                 ,@UserID=CreatedByID     FROM ReportServer.dbo.[Catalog] WHERE [Name] = 'System Resources';    --Delete all existing    DELETE FROM ReportServer.dbo.[Catalog] WHERE [Path] LIKE '%' +  @BrandingType + '%';    --New Variables    DECLARE @NewZipID UNIQUEIDENTIFIER = NEWID();    DECLARE @NewPathID UNIQUEIDENTIFIER = NEWID();    DECLARE @NewPath VARCHAR(100) = '/' + LOWER(CAST(NEWID() AS VARCHAR(100)));    DECLARE @NewPathName VARCHAR(100) ='fbac82c8-9bad-4dba-929f-c04e7ca4111f'; --It seems, that this special GUID is needed, otherwise no Logo is displayed    DECLARE @NewBrandID UNIQUEIDENTIFIER = NEWID();    DECLARE @ColorsID UNIQUEIDENTIFIER = NEWID();    DECLARE @LogoID UNIQUEIDENTIFIER = NEWID();    DECLARE @dt DATETIME=GETDATE();    DECLARE @BrandProperties NVARCHAR(MAX)=    CAST((        SELECT @BrandingType AS [Resource.Type]              ,@BrandingName AS [Resource.Name]              ,@BrandingVersion AS [Resource.Version]              ,LOWER(CAST(@NewZipID AS VARCHAR(100))) AS [Resource.PackageId]              ,LOWER(CAST(@ColorsID AS VARCHAR(100))) AS [Item.colors]              ,LOWER(CAST(@LogoID AS VARCHAR(100))) AS [Item.logo]        FOR XML PATH('Properties'),TYPE    ) AS NVARCHAR(MAX));    --Universal Brand    INSERT INTO ReportServer.dbo.[Catalog](ItemID,[Path],[Name],ParentID,[Type]               ,Content               ,Property               ,[Hidden],CreatedByID,CreationDate,ModifiedByID,ModifiedDate               ,MimeType,PolicyID,PolicyRoot,ExecutionFlag)      VALUES(@NewBrandID,@SystemResourcePath + '/' + @BrandingType, @BrandingType, @SystemResourceID,1          ,NULL          ,@BrandProperties          ,0,@UserID,@dt,@UserID,@dt,NULL,@PolicyID,0,1);    --ZIP file dummy    DECLARE @currentPath VARCHAR(500) = @SystemResourcePath + '/' +  @BrandingType + '/' + @BrandingZIPName;    INSERT INTO ReportServer.dbo.[Catalog](ItemID,[Path],[Name],ParentID,[Type]               ,Content               ,Property               ,[Hidden],CreatedByID,CreationDate,ModifiedByID,ModifiedDate               ,MimeType,PolicyID,PolicyRoot,ExecutionFlag)      VALUES(@NewZipID,@currentPath,@BrandingZIPName,@NewBrandID,3          ,@BrandingZIP          ,'<Properties />'          ,0,@UserID,@dt,@UserID,@dt,'application/octet-stream',@PolicyID,0,1);    --Brand path    SET @currentPath = @SystemResourcePath + '/' +  @BrandingType + '/' + @NewPathName;    INSERT INTO ReportServer.dbo.[Catalog](ItemID,[Path],[Name],ParentID,[Type]               ,Content               ,Property               ,[Hidden],CreatedByID,CreationDate,ModifiedByID,ModifiedDate               ,MimeType,PolicyID,PolicyRoot,ExecutionFlag)      VALUES(@NewPathID,@currentPath,@NewPathName,@NewBrandID,1          ,NULL          ,'<Properties />'          ,0,@UserID,@dt,@UserID,@dt,NULL,@PolicyID,0,1);    --colors    INSERT INTO ReportServer.dbo.[Catalog](ItemID,[Path],[Name],ParentID,[Type]               ,Content               ,Property               ,[Hidden],CreatedByID,CreationDate,ModifiedByID,ModifiedDate               ,MimeType,PolicyID,PolicyRoot,ExecutionFlag)      VALUES(@ColorsID,@currentPath + '/colors','colors',@NewPathID,3          ,@BrandingColorsJSON          ,'<Properties />'          ,0,@UserID,@dt,@UserID,@dt,'application/octet-stream',@PolicyID,0,1);    --logo    INSERT INTO ReportServer.dbo.[Catalog](ItemID,[Path],[Name],ParentID,[Type]               ,Content               ,Property               ,[Hidden],CreatedByID,CreationDate,ModifiedByID,ModifiedDate               ,MimeType,PolicyID,PolicyRoot,ExecutionFlag)      VALUES(@LogoID,@currentPath + '/logo','logo',@NewPathID,3          ,@BrandingLogoPNG          ,'<Properties />'          ,0,@UserID,@dt,@UserID,@dt,'image/png',@PolicyID,0,1);    COMMIT;END

And with this PowerShell lines I unzip the package, extract the parameters and call the procedure:

#Unzip BrandingPackage Add-Type -AssemblyName System.IO.Compression.FileSystemAdd-Type -AssemblyName System.Collections$BrandingPackagePath = ($PSScriptRoot + "\FrequentisReportCenter.zip")$BrandingPackageUnzipped = ($PSScriptRoot + "\FrequentisReportCenter.zip.Unzipped")[System.IO.Compression.ZipFile]::ExtractToDirectory($BrandingPackagePath,$BrandingPackageUnzipped)Write-Host "BrandingPackage unzipped"$xml = New-Object System.Xml.XmlDocument$nsMngr = New-Object System.Xml.XmlNamespaceManager($xml.NameTable)$nsMngr.AddNamespace("ns","http://schemas.microsoft.com/sqlserver/reporting/2016/01/systemresourcepackagemetadata")$xml.Load(($BrandingPackageUnzipped + "\" + "metadata.xml"))$BrandingType=$xml["SystemResourcePackage"].Attributes["type"].Value$BrandingVersion=$xml["SystemResourcePackage"].Attributes["version"].Value$BrandingName=$xml["SystemResourcePackage"].Attributes["name"].Value$PathColors=($BrandingPackageUnzipped + "\" + $xml.SelectNodes("//ns:Contents/ns:Item[@key='colors']",$nsMngr)[0].Attributes["path"].Value)$PathLogo=($BrandingPackageUnzipped + "\" + $xml.SelectNodes("//ns:Contents/ns:Item[@key='logo']",$nsMngr)[0].Attributes["path"].Value)#BrandingPackage values fetched$cmd.CommandText="MyDatabase.dbo.InstallBrandingPackage `                                                  @BrandingZIPPath=@ZIPPath,`                                                  @BrandingColorsPath=@ColorsPath,`                                                  @BrandingLogoPath=@LogoPath,`                                                  @BrandingType=@Type,`                                                  @BrandingName=@Name,`                                                  @BrandingVersion=@Version"$cmd.Parameters.AddWithValue("@ZIPPath",$BrandingPackagePath)$cmd.Parameters.AddWithValue("@ColorsPath",$PathColors)$cmd.Parameters.AddWithValue("@LogoPath",$PathLogo)$cmd.Parameters.AddWithValue("@Type",$BrandingType)$cmd.Parameters.AddWithValue("@Name",$BrandingName)$cmd.Parameters.AddWithValue("@Version",$BrandingVersion)$cmd.ExecuteNonQuery()#BrandingPackage written