Deploy SSRS reports on a customer's machine with PowerShell

Question

We are developing some reports locally. These reports are to be installed on a customer's machine. But we cannot reach this machine online.

What I've managed so far:

A folder containing:

• all report objects (data source, data sets and reports)
• a copy of the PowerShell module ReportingServicesTools
• a PowerShell script

This script will create all needed folders within ReportServer, load the data source, then the data sets and finally the reports. This works, I can open the portal and can see my objects.

UPDATE:

The downloaded rdl files had their data-set-references set to the plain dataset's name - why ever... It took me hours to find, that the reference must include the full (internal) path to the dataset... Now the paginated reports really work, heureka!

The Questions

This does not work for

• mobile reports (aka dashboards, but not PowerBI)
• our branding file

How can mobile reports be uploaded via PowerShell?
How can a custom branding package be uploaded?
Is there a better approach? I'm fairly new to PowerShell and therefore open to any advice! TIA

EDIT

Deleted the script fragment as I've posted a comprehensive answer myself...

UPDATE: No answer yet?

At the bottom of my own answer there are some open questions... I'd love to give the bounty to somebody but it seems to be lost... Anyway: If Anybode might answer at least part of the remaining open questions there's a good chance for 150 points :-)

Fawcett

Answer 1

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  .\ReportingServicesTools
New-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 = $con
Write-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.FileSystem
Add-Type -AssemblyName System.Collections
$unzippedList = New-Object System.Collections.ArrayList
Class 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?

Fawcett

Answer 2

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)

)
AS
BEGIN
    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.FileSystem
Add-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

Fawcett