Migrating SSRS content with PowerShell

With a distinct lack of up-to-date, fully featured or built-in options to get Reporting Services content cleanly from A to B, it can often be a challenging task maintaining proper Development and QA environments or even moving reports from a SharePoint integrated installation to a native mode one, and vice versa.

I want to explore the two most efficient methods of bulk-migrating Reporting Services content & also explore other options I’ve used over the years and those that have come and gone.

RS Scripter

You may still come across this first migration tool if you search for Reporting Services Migration or similar. There are several highly ranked links with publication dates in the last 5 years too! Don’t be fooled though, this tool is now 12 years old. The .NET application was created by SQL Server MVP Jasper Smith. This article “The Reporting Services Scripter”, (written by Kevin Kline back in 2006) describes the tool with a now defunct link to its original download page. (WebArchive)

This was a great tool to easily identify content in SSRS & migrate it directly to a target instance or download it to be uploaded later. It’s a shame it’s not still maintained as it became obsolete with the introduction of SQL Server 2008 R2. These new components and anything introduced since is not supported by RSScripter so they won’t be migrated. (Eg. Linked Reports & History)

You can still easily find the tool in a host of locations and it works fine for your normal folders, reports and data sources (though not the credentials).

Reporting Services Migration Tool

This tool is often another high ranking result when searching for SSRS migration tools. Released by Microsoft in 2012, it looks like a promising, “official” result. “Perfect! There is hope!”…maybe not.

This tool works by generating a full array of PowerShell scripts based on your source content, whether that be from a native or SharePoint integrated instance.

The first main roadblock is that you can only deploy to a SharePoint Integrated instance. Even though the PowerShell scripts are right there for you to edit, the source content is all explicitly called out and any potential alterations to force a deployment to a Native mode instance will involve a great deal of time editing and debugging. (Yes, I’ve spent hours doing this before!).

If your target instance is a SharePoint integrated installation then great! Read no further. It’s not the easiest application to use but it will get you from A to B and you can dissect & edit the PowerShell code if you want to customize your migration.

One feature that this tool has above almost all of the rest is that you can include Report History (if this is a feature you use) and not just the history settings. Although that does come with a stern warning:

Please note that migrating report history requires direct modification to data in the report server catalog and is not publicly supported. Backing up the catalog before migration and verifying correctness of the reports after migration are strongly recommended.

You can still find the Reporting Services Migration Tool hosted by Microsoft but there seems to have been no further development beyond the initial 2012 release despite this line in the description:

Target server must be SharePoint integrated mode. (We are working on support for native mode.)

Rs.exe VB Script

This Microsoft Page “Sample Reporting Services rs.exe Script to Copy Content between Report Servers” describes a sample script created by Benjamin Satzger at Microsoft, to migrate content between two Report Servers using the rs.exe application present in both modes of Reporting Services installation.

It does cover some of the shortfalls of the RSScripter tool & it can also migrate content to a native mode installation, unlike the Reporting Services Migration Tool (but it will not migrate report history).

There is some great documentation & details on the above Microsoft Docs page but it looks like the download link hasn’t (yet?) been updated from Codeplex, though the supported SQL Server versions do go right up to 2016 which is promising.

The script is all written in VB.NET using the Reporting Services SOAP API. It’s not as easy to use or as flexible as the PowerShell module but it’s certainly a more complete and relevant option than the two I have mentioned thus far. No prior knowledge of VB.NET is required which is also a bonus.

Constant backfill

This is a workaround I have personally adopted for a while but relates mainly to keeping various environments in line, and not a core migration method like the others. I wanted to mention it though as the migration of SSRS content is usually driven by the need to keep multiple non-production environments in line.

This method became part of my BI agile release process about a year ago though it is not without its own drawbacks. Prior to its inception, several days of my time were swallowed up re-building DEV or QA environments when they became too far out of date or some piece of work had impacted the content on a Report Server. I’d have to copy down our production database, remove or update the Scale-out deployment contents (servers), re-point every data source we have, manually (over 30!) to its DEV or QA counterpart & update any stored credentials. Then we’d have to update folder security to remove all the production permissions. This would also bring the environment down for the day or more that it took to get everything in place.

While moving to an agile deployment method I began exploring better ways to keep our environments in line. If no releases are missed, backfilling at the time of each production release was the least time-consuming method of keeping all our DEV & QA environments up to production version & could actually be automated easily with PowerShell or as part of a CI/CD process.

Report kits are deployed to DEV & then QA
Once approved, a kit gets deployed to Production
Kit is then automatically deployed to cold QA & DEV environments with the same production code.

The main benefit of using this “backfill” method is that it removes the need, in most cases, to restore an entire database, recovering encryption keys, re-pointing data sources & altering security which can take more than a few hours to complete manually, depending on the size of your SSRS instance.

dbatools PowerShell module

This is a bulk method approach but much more efficient than doing a database restore through Management Studio. It is literally a single command.

1	Copy-DbaDatabase -Source DBSOURCE\Instance -Destination DBTARGET\Instance -Database ReportServer -IncludeSupportDbs -WithReplace -BackupRestore -NetworkShare \\Share\SSRS_Migration

The -IncludeSupportDBs flag I use here is actually designed to include the ReportServer and ReportServerTempDB anyway but seeing as those are the only 2 I am migrating, I have called out the ReportServer DB and I’m using the flag to bring the TempDB over.

You can then augment this script with the Reporting Services module to fix data source connection strings & make any further environment specific changes, which is actually my preferred migration method detailed in the conclusion below.

Although dbatools doesn’t cater to Reporting Services specifically, it’s a very powerful module for interacting with databases. You can find a full command list on the dbatools site

Reporting Services PowerShell module

Similar in functionality and flexibility as the old RS Scripter, the official PowerShell module allows you to download & upload individual items, specific folders or a full instance of content. You can also utilize the new REST API for SQL Server 2016 installations. Here are a few code chunks for tasks you might perform during a migration.

Migrate all SSRS folders

This code chunk will migrate from a source environment, down to a local temporary folder then back up to the destination environment. Setting the $Recurse variable to $true will add that flag to the commands and include subfolder contents too. It’s a great way to quickly migrate content but be careful to note that this only migrates reports, shared data sources & datasets.

$SourceUri = 'http://SOURCE_SSRS/reportserver/ReportService2010.asmx?wsdl'

$DestUri = 'http://DESTINATION_SSRS/reportserver/ReportService2010.asmx?wsdl'

try {

$SourceProxy = New-RsWebServiceProxy -ReportServerUri $SourceUri

$DestProxy = New-RsWebServiceProxy -ReportServerUri $DestUri

if($Recurse){

Out-RsFolderContent -Proxy $SourceProxy -RsFolder $RsFolder -Destination $tempFolder -Recurse

Write-RsFolderContent -Proxy $DestProxy -RsFolder $RsFolder -Path $tempFolder -Recurse -Overwrite

}

else {

Out-RsFolderContent -Proxy $SourceProxy -RsFolder $RsFolder -Destination $tempFolder

Write-RsFolderContent -Proxy $DestProxy -RsFolder $RsFolder -Path $tempFolder -Overwrite

}

catch {

throw $_.Exception.Message

}

Restore or backup an Encryption Key

Connect-RsReportServer -ComputerName 'SSRSComputerName' -ReportServerInstance 'MSSQLSERVER' -ReportServerUri 'http://ReportingURL/reportserver/ReportService2010.asmx?wsdl' -ReportServerVersion 12

Write-Verbose "Backup Encryption Key"

$encKey = Read-Host "Please provide a location to store the new encryption key (E.g. C:\Encrypt_Key.snk)"

$encKeyPass = Read-Host "Please provide the Password for the Encryption Key"

Write-Debug "Encryption Key location: $($encKey)"

Backup-RSEncryptionKey -Password $encKeyPass -KeyPath $encKey

Restore-RSEncryptionKey -Password $encKeyPass -KeyPath $encKey

I connect to the Report Server first, before restoring the key so that I don’t need to define the Report Server version, which is set in the Connect command. If you have dbatools however, you can get the version using the following command:

1	$SQLVersion = Get-DbaSqlInstanceProperty -SqlInstance $targetInstance \| Where-Object{$_.Name -eq 'VersionMajor'}

Update Data Sources from a reference table

In this script excerpt, I utilize a process I have implemented to backup all shared data source information within SSRS to a database table. See the Appendix below for that script. I then prompt the user for all of the passwords so we aren’t storing them anywhere. You can consider using the hosts file to negate the need to update connection strings but we’ve opted not to use that method.

100

101

102

103

104

105

106

107

108

[CmdletBinding()]

param (

[Parameter(Mandatory=$true)]

[string]

$ReportServerUri,

[string]

$range,

#Database settings

[Parameter(Mandatory=$true)]

[string]

$DBserver,

[Parameter(Mandatory=$true)]

[string]

$Database,

[Parameter(Mandatory=$true)]

[string]

$schema,

[Parameter(Mandatory=$true)]

[string]

$table

)

begin {

$moduleName = 'ReportingServicesTools'

Try{

if (Get-Module -ListAvailable -Name $moduleName){

Import-Module -Name $moduleName -ErrorAction SilentlyContinue

}

else{

Install-Module -Name $moduleName -ErrorAction SilentlyContinue -Scope CurrentUser

}

Catch{

throw "$($moduleName) module is not installed and could not be added"

}

process {

try{

Write-Verbose "Connect to the Web service"

$proxy = New-RsWebServiceProxy -ReportServerUri $reportServerUri

Write-Verbose "Open DB connection for data source reference info"

$ConnSELECT = New-Object System.Data.SQLClient.SQLConnection

$ConnSELECT.ConnectionString = "server='$DBserver';database='$Database';trusted_connection=true;"

$ConnSELECT.Open()

Write-Verbose "Define Query to pull data source settings from source table"

$sqlSELECT = "SELECT

[Path]

,[Enabled]

,[ConnectionString]

,[UserName]

,[CredentialRetrieval]

FROM $database.$schema.$sTable

WHERE Range = '$range'

AND Enabled = 'True'"

$Command = New-Object System.Data.SQLClient.SQLCommand($sqlSELECT, $ConnSELECT)

$references = @{}

$references = $Command.ExecuteReader()

}

catch{

throw (New-Object System.Exception("Error retrieving data from $($database).$($schema).$($table)! $($_.Exception.Message)", $_.Exception))

}

Write-Verbose "Loop through each data source row to update target SSRS Data Source"

foreach($row in $references) {

try {

$datasourcePath = $row.Item("Path")

Write-Verbose "Retrieve Definition for data source $datasourcePath"

$DataSourceDefinition = Get-RsDataSource -Proxy $proxy -Path $datasourcePath

if($row.Item("CredentialRetrieval") -eq 'Integrated'){

Write-Verbose "Updating Connection String"

$DataSourceDefinition.ConnectString = $row.Item("ConnectionString")

Set-RsDataSource -Proxy $proxy -RsItem $datasourcePath -DataSourceDefinition $DataSourceDefinition

Write-Verbose "Connection String Updated!"

}

elseif($row.Item("CredentialRetrieval") -eq 'Store'){

Write-Verbose "Updating Connection String and Stored Credentials"

$DataSourceDefinition.ConnectString = $row.Item("ConnectionString")

$DataSourceDefinition.UserName = $row.Item("UserName")

Set-RsDataSource -Proxy $proxy -RsItem $datasourcePath -DataSourceDefinition $DataSourceDefinition

Write-Verbose "Connection String Updated!"

$password = Read-Host "Password for $($row.Item('UserName'))"

Set-RsDataSourcePassword -Proxy $proxy -Path $datasourcePath -Password $password

Write-Verbose "Stored Credentials Updated!"

}

catch {

Write-Error "Error $_"

}

$ConnSELECT.Close()

}

end {

}

You can also find a full command list on the module’s GitHub page

Conclusion

I find that every method has its drawbacks and even though the backfill process I use works great to keep non-production environments up to date, it won’t account for all of the content that never makes it to production or any removal or edits of reports, folders etc that can happen in development environments. This is where I utilize a hybrid of dbatools and the Reporting Services module

Restore the RS Database

Copy or Restore a Database backup using dbatools
Set the SSRS Database (to implement Security, subscriptions etc)

Restore the source encryption key to the new environment

1	.\Scripts\Restore-RSDatabase.ps1 -reportServer 'http://ReportingURL/Reportserver' -targetInstance 'DBSERVER\INSTANCE' -targetDatabase 'ReportServer' -backupLocation '\\Share\Backup\ReportServerFolder' -ssrsServer 'SSRSSERVER'

Delete all subscriptions copied down with the DB from Production (if desired) *[Remember to set up the proxy connection first]

1

Remove-RSSubscriptionBulk -RSfolder '/' -Recurse -proxy $Proxy
Update Data sources from a reference table (As above)
Update folder security for the relevant environment. (I want to revert security to Inherit for a DEV environment in this case)

1

Set-RSFolderSecurity -Proxy $proxy -RsFolder '/' -Action Inherit –Recurse
Remove unwanted servers from the scale-out deployment

1

Reset-RSScaleOut -targetInstance 'DBSERVER\INSTANCE' -targetDatabase 'ReportServer' -ssrsServer 'SSRSSERVER'
Backup the encryption key (As Above)

Although there are a few steps in this method, I can manage each stage individually and the full process is done in under an hour with no step being performed manually which is the aim of the game! Depending on your environment you could even string these together into a single script for faster execution

Here are the functions and scripts for the full process described above. This can also be found on my GitHub page

Restore-RSDatabase

.EXAMPLE

Restore-RSDatabase.ps1 -reportServer 'http://ReportingURL/Reportserver' -targetInstance 'DBSERVER\DBINSTANCE' -targetDatabase 'ReportServer' `

-backupLocation '\\Networkshare\ReportServer' -ssrsServer 'SSRS01' -Verbose

[CmdLetBinding()]

param

(

[string]

$reportServer,

[string]

$targetInstance,

[string]

$targetDatabase,

[string]

$backupLocation,

[string]

$ssrsServer

)

$InformationPreference = 'Continue'

#-----------------------------------------------------------------

Write-Information "Migrate the DB using dbaTools"

#-----------------------------------------------------------------

try {

Write-Verbose "Restoring Database from $($backupLocation)"

Restore-DbaDatabase -SqlServer $targetInstance -Path $backupLocation -WithReplace

Write-Verbose "Database restored to $($targetDatabase) on $($targetInstance)"

}

catch {

throw (New-Object System.Exception("Error restoring from backup! $($_.Exception.Message)", $_.Exception))

}

#-----------------------------------------------------------------

Write-Information "Set RS Database"

#-----------------------------------------------------------------

try {

Write-Verbose "Get SQL Version for $($targetInstance)"

$SQLVersion = Get-DbaSqlInstanceProperty -SqlInstance $targetInstance | Where-Object{$_.Name -eq 'VersionMajor'}

Write-Verbose "Connecting to $($reportServer) default instance"

Connect-RsReportServer -ComputerName $ssrsServer -ReportServerInstance 'MSSQLSERVER' -ReportServerUri $reportServer

Write-Verbose "Setting up RS Database $($targetDatabase) Version:$($SQLVersion.Value) on $($targetInstance)"

Set-RsDatabase -DatabaseServerName $targetInstance -Name $targetDatabase -IsExistingDatabase -DatabaseCredentialType ServiceAccount -ReportServerVersion $SQLVersion.Value

}

catch {

throw (New-Object System.Exception("Error setting RS Database! $($_.Exception.Message)", $_.Exception))

}

#-----------------------------------------------------------------

Write-Information "Restore the Encryption Key"

#-----------------------------------------------------------------

try {

Write-Verbose "Restoring Encryption Key from Source"

$encKey = Read-Host "Please provide local encryption key for Database Source (E.g. C:\Encrypt_Key.snk)"

$encKeyPass = Read-Host "Please provide the Password for the Encryption Key"

Write-Debug "Encryption Key location: $($encKey)"

Restore-RSEncryptionKey -Password $encKeyPass -KeyPath $encKey -ReportServerVersion $SQLVersion.Value

Write-Information "Please remember to update the Scaled out servers in SSRS"

}

catch{

throw (New-Object System.Exception("Error restoring Encryption Key! $($_.Exception.Message)", $_.Exception))

}

$InformationPreference = 'Silently Continue'

Set-RSFolderSecurity

100

101

102

103

104

105

106

107

108

109

110

111

112

.SYNOPSIS

This will set the security on a specified folder. This can be adding or removing a user, or setting to inherit from parent

.DESCRIPTION

Sets security on a specified folder. Use of the Action variable will allow the addition or removal of a specified user or group. Using 'Inherit' will return the folder security

to that of the parent folder.

.PARAMETER RsFolder

Target folder. This should be preceded by a /. Eg. '/Sales Reports'. It is possible to set the Root folder using '/'

.PARAMETER Identity

This is the username of the user or group to be added or removed. This should include a domain prefix where relevant

.PARAMETER Role

The chosen Role for the addition of a new user. This should be one of the set roles within the target instance

.PARAMETER Action

This should be one of 3 options. ADD a provided user & role. REMOVE a provided user. Set the folder to Inherit from Parent

.PARAMETER Recurse

Flag to determine if all subfolders should be included or only the target folder

.PARAMETER proxy

Proxy object provided by using the New-RsWebServiceProxy command. Eg. New-RsWebServiceProxy -ReportServerUri 'http://ReportServerURL/ReportServer/ReportService2010.asmx?wsdl'

.EXAMPLE

Set-RSFolderSecurity -Proxy $proxy -RsFolder '/' -Action Add -Identity 'DOMAIN\User' -Role 'Content Manager'

This will grant DOMAIN\User Content Manager permissions on the root folder Only.

.NOTES

NOTE: The user executing this function will need the permissions within SSRS to perform these security changes

function Set-RSFolderSecurity {

[CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'Medium')]

param (

[Alias('ItemPath', 'Path')]

[Parameter(Mandatory = $True)]

[string]

$RsFolder,

[Alias('GroupUserName')]

[string]

$Identity,

[Alias('RoleName')]

[string]

$Role,

[ValidateSet('Add','Remove','Inherit')]

[string]

$Action,

[switch]

$Recurse,

$proxy

)

begin {

If($Identity -eq $null -and (($Action -eq 'Add' -and $role -eq $null) -or ($Action -eq 'Remove'))){

throw (New-Object System.Exception("Missing Parameters!"))

}

process {

$folders = @()

try{

if($Recurse){

Write-Verbose "List out all sub-folders under the $($RsFolder) directory"

$RSFolders = Get-RsFolderContent -Proxy $proxy -Path $RSFolder -Recurse | Where-Object{$_.TypeName -eq 'Folder'}

$folders = $RSFolders.Path

}

Write-Verbose "Adding Folder Parameter to Array"

$folders += $RsFolder

foreach($folder in $folders){

If($Action -eq 'Add'){

Write-Verbose "Granting $($Identity) $($role) permissions on $($folder)"

if ($pscmdlet.ShouldProcess($folder, "Granting $($Identity) $($role) permissions on $($folder)")) {

Grant-RsCatalogItemRole -Proxy $proxy -Identity $Identity -RoleName $Role -Path $folder

}

Elseif($Action -eq 'Remove'){

Write-Verbose "Removing $($Identity) permissions from $($folder)"

if ($pscmdlet.ShouldProcess($folder, "Remove $($Identity) permissions from $($folder)")) {

Revoke-RsCatalogItemAccess -Proxy $proxy -Identity $Identity -Path $folder

}

ElseIf($Action -eq 'Inherit'){

$InheritParent = $true

Write-Verbose "Setting $($folder) to Inherit Parent Security"

$Proxy.GetPolicies($folder, [ref]$InheritParent)

if(-not $InheritParent -and $folder -ne '/') #Cant revert perms on Root folder

{

if ($pscmdlet.ShouldProcess($folder, "Set $($folder) to Inherit from Parent")) {

$Proxy.InheritParentSecurity($folder)

}

Else{

throw (New-Object System.Exception("No Valid Action provided! Use Add | Remove | Inherit"))

}

catch{

throw (New-Object System.Exception("Error Updating Permissions! $($_.Exception.Message)", $_.Exception))

}

end {

}

Reset-RSScaleOut

.SYNOPSIS

Removes all but the specified servers from Reporting Services Scale Out deployment

.DESCRIPTION

Using dbatools Invoke-DbaSqlCmd command removes all servers except the specified servers from the dbo.keys table in the SSRS database.

This removes them from the Scale-Out deployment. This is intended to be used in Migration situations

.PARAMETER targetInstance

This is the target database instance. For default instances, you do not need to specify the instance. Eg. DBSERVER Eg. DBSERVER\INSTANCE2

.PARAMETER targetDatabase

This is the target Database for the Report Server. This defaults to ReportServer if no DB is provided

.PARAMETER ssrsServer

This is the machine name for the target servers. These can be passed as an array or a single machine

.EXAMPLE

Reset-RSScaleOut -targetInstance 'DBSERVER' -targetDatabase ReportServerClient1 -ssrsServer 'SSRS01'

This will remove all other machines from the Scale-Out, leaving only SSRS01

.NOTES

General notes

function Reset-RSScaleOut {

[CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'Medium')]

param (

[string]

$targetInstance,

[string]

$targetDatabase = 'ReportServer',

[string[]]

$ssrsServer

)

begin {

$serverlist = $null

foreach($server in $ssrsServer){

$serverlist += "'$($server)',"

}

$serverlist = $serverlist.TrimEnd(",")

}

process {

try {

Write-Verbose "Delete all Scale Out Keys not attributed to servers: $($serverlist)"

$command = "DELETE FROM [$($targetDatabase)].[dbo].[Keys] WHERE MachineName NOT IN ($($serverlist))"

Write-Verbose "Command: $($command)"

if ($pscmdlet.ShouldProcess($command, "Delete Machines from Scale Out deployment (dbo.Keys table)")) {

Invoke-DbaSqlCmd -SqlInstance $targetInstance -Query $command

}

Invoke-DbaSqlCmd -SqlInstance $targetInstance -Query $command

}

catch {

throw (New-Object System.Exception("Error Cleaning Keys Table! $($_.Exception.Message)", $_.Exception))

}

end {

}

Remove-RSSubscription

.SYNOPSIS

Removes all subscriptions from a provided folder

.DESCRIPTION

This removes all subscriptions in a provided folder with a Recurse flag to include all subfolders.

.PARAMETER RsFolder

Target folder. This should be preceded by a /. Eg. '/Sales Reports'. It is possible to set the Root folder using '/'

.PARAMETER Recurse

Flag to determine if all subfolders should be included or only the target folder

.PARAMETER Proxy

Proxy object provided by using the New-RsWebServiceProxy command. Eg. New-RsWebServiceProxy -ReportServerUri 'http://ReportServerURL/ReportServer/ReportService2010.asmx?wsdl'

.EXAMPLE

Remove-RSSubscriptionBulk -RSfolder '/' -Recurse -proxy $Proxy

This will remove all subscriptions in an entire instance

.EXAMPLE

Remove-RSSubscriptionBulk -RSfolder '/Sales Reports' -proxy $Proxy -Confirm

This will remove all subscriptions in the Sales Reports folder only. It will not affect subfolders. It will also prompt before each subscription deletion

.NOTES

General notes

function Remove-RSSubscriptionBulk{

[CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'Medium')]

param (

[Alias('ItemPath', 'Path')]

[Parameter(Mandatory = $True)]

[string]

$RsFolder,

[switch]

$Recurse,

$Proxy

)

begin {

#$Proxy = New-RsWebServiceProxyHelper -BoundParameters $PSBoundParameters

}

process {

try {

if($Recurse){

Write-Verbose "Recurse flag set. Return all subscriptions in Folder:$($RsFolder) and sub-folders"

$subs = $Proxy.ListSubscriptions($RsFolder)

}

else{

Write-Verbose "Recurse flag not set. Return all subscriptions in Folder:$($RsFolder) only"

$subs = $Proxy.ListSubscriptions($RSFolder) | Where-Object {$_.Path -eq "$($RsFolder)/$($_.Report)"}

}

catch {

throw (New-Object System.Exception("Failed to retrieve items in '$RsFolder': $($_.Exception.Message)", $_.Exception))

}

try {

Write-Verbose "$($subs.Count) Subscriptions will be deleted."

foreach($sub in $subs){

if ($pscmdlet.ShouldProcess($sub.Path, "Delete Subscription with ID: $($sub.SubscriptionID)")) {

$Proxy.DeleteSubscription($sub.SubscriptionID)

}

Write-Verbose "Subscription Deleted: $($sub.SubscriptionID)"

}

catch {

throw (New-Object System.Exception("Failed to delete items in '$RsFolder': $($_.Exception.Message)", $_.Exception))

}

end {

}

References

Appendix

SSRS Data Source Backup PowerShell Script

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

[CmdletBinding()]

param

(

[Parameter(Mandatory=$true)]

[string]

$ReportServerUri,

[Parameter(Mandatory=$true)]

[string]

$DataSourceFolder,

[string]

$range,

#Database settings

[Parameter(Mandatory=$true)]

[string]

$DBserver,

[Parameter(Mandatory=$true)]

[string]

$Database,

[Parameter(Mandatory=$true)]

[string]

$schema,

[Parameter(Mandatory=$true)]

[string]

$table

)

begin{

$moduleName = 'ReportingServicesTools'

Try{

if (Get-Module -ListAvailable -Name $moduleName){

Import-Module -Name $moduleName -ErrorAction SilentlyContinue

}

else{

Install-Module -Name $moduleName -ErrorAction SilentlyContinue -Scope CurrentUser

}

Catch

{

throw "$($moduleName) module is not installed and could not be added"

}

if(!$DataSourceFolder.StartsWith('/')){

Write-Verbose "Data Source Folder did not starts with /. Updating!"

$DataSourceFolder = "/$($DataSourceFolder)"

}

if($range -eq $null){

$range = 'NOT DEFINED'

}

process{

try{

$proxy = New-RsWebServiceProxy -ReportServerUri $reportServerUri

$DSContents = $Proxy.ListChildren($DataSourceFolder,$false) | Where-Object{$_.TypeName -eq 'DataSource'}

$SSRSDataSourceElements = @()

foreach($SSRSDataSourceElement in $DSContents)

{

$TempSSRSMeta = $Proxy.GetDataSourceContents($SSRSDataSourceElement.Path)

$SSRSDataSourceElements += New-Object PSObject -Property @{

'range' = $range

'UserName' = $TempSSRSMeta.UserName;

'Enabled' = $TempSSRSMeta.Enabled;

'WindowsCredentials' = $TempSSRSMeta.WindowsCredentials;

'CredentialRetrieval' = $TempSSRSMeta.CredentialRetrieval;

'Name' = $SSRSDataSourceElement.Name;

'Path' = $SSRSDataSourceElement.Path;

'Description' = $SSRSDataSourceElement.Description;

'ConnectionString' = $TempSSRSMeta.ConnectString;

'Extension' = $TempSSRSMeta.Extension;

'DateChecked' = Get-Date -Format s;

}

catch {

throw (New-Object System.Exception("Error adding data sources to Array! $($_.Exception.Message)", $_.Exception))

}

#-----------------------------------------------------------------------

# Connect to SQL table and insert the new contents

#-----------------------------------------------------------------------

try{

Write-Verbose "Open connection to Database server"

$ConnINSERT = New-Object System.Data.SQLClient.SQLConnection

$ConnINSERT.ConnectionString = "server='$DBserver'; Database='$Database';Trusted_Connection=true;"

$ConnINSERT.Open()

#-------------------------------------------------------------------------------

Write-Verbose "Create destination table if it does not exist"

$sqlCREATE = "IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$($schema).$($table)'))

CREATE TABLE $($schema).$($table)(

[Range] [NVARCHAR](50) NULL,

[Name] [NVARCHAR](50) NOT NULL,

[Path] [NVARCHAR](200) NOT NULL,

[Enabled] [NVARCHAR](5) NOT NULL,

[Description] [NVARCHAR](200) NULL,

[ConnectionString] [NVARCHAR](200) NULL,

[Extension] [NVARCHAR](50) NULL,

[UserName] [NVARCHAR](50) NOT NULL,

[Windows Credentials] [NVARCHAR](5) NOT NULL,

[CredentialRetrieval] [NVARCHAR](30) NOT NULL,

[DateChecked] [DATETIME2](7) NULL

$Command = New-Object System.Data.SQLClient.SQLCommand($sqlCREATE, $ConnINSERT)

Write-Debug $Command.CommandText

$numROWS = $Command.ExecuteNonQuery()

#-------------------------------------------------------------------------------

}

catch{

throw (New-Object System.Exception("Error creating Table in $($Database)! $($_.Exception.Message)", $_.Exception))

}

try{

foreach($Field in $SSRSDataSourceElements)

{

#Escape characters in expected fields

if($($Field.Description)){$Field.Description = $Field.Description.replace("'","''")}

$Field.ConnectionString = $Field.ConnectionString.replace("'","''")

$sqlINSERT = "IF NOT EXISTS (SELECT 1 FROM $database.$schema.$table WHERE Name = '$($Field.Name)' AND Path = '$($Field.Path)' AND Range = '$($range)')

BEGIN

INSERT INTO $database.$schema.$table

([Range]

,[Name]

,[Path]

,[Enabled]

,[Description]

,[ConnectionString]

,[Extension]

,[UserName]

,[Windows Credentials]

,[CredentialRetrieval]

,[DateChecked])

VALUES

('$($range)'

,'$($Field.Name)'

,'$($Field.Path)'

,'$($Field.Enabled)'

,'$($Field.Description)'

,'$($Field.ConnectionString)'

,'$($Field.Extension)'

,'$($Field.UserName)'

,'$($Field.WindowsCredentials)'

,'$($Field.CredentialRetrieval)'

,'$($Field.DateChecked)')

END

ELSE IF EXISTS (SELECT 1 FROM $database.$schema.$table WHERE Name = '$($Field.Name)' AND Path = '$($Field.Path)' AND Range = '$($range)')

BEGIN

UPDATE $database.$schema.$table

SET

[Enabled] = '$($Field.Enabled)',

[Description] = '$($Field.Description)',

[ConnectionString] = '$($Field.ConnectionString)',

[Extension] = '$($Field.Extension)',

[UserName] = '$($Field.UserName)',

[Windows Credentials] = '$($Field.WindowsCredentials)',

[CredentialRetrieval] = '$($Field.CredentialRetrieval)',

[DateChecked] = '$($Field.DateChecked)'

WHERE Name = '$($Field.Name)' AND Path = '$($Field.Path)' AND Range = '$($range)'

END"

$Command = New-Object System.Data.SQLClient.SQLCommand($sqlINSERT, $ConnINSERT)

$numROWS = $Command.ExecuteNonQuery()

}

$Command.Dispose()

$ConnINSERT.Close()

$ConnINSERT.Dispose()

}

catch {

#Data Backup failure

throw (New-Object System.Exception("Error adding data to $($database).$($schema).$($table)! $($_.Exception.Message)", $_.Exception))

}

Author
Recent Posts

Craig Porteous

Craig is a Microsoft certified BI Developer & Administrator and has worked with the Microsoft SQL stack for over 8 years. From working with cloud technologies like AWS & PowerBI to managing enterprise level Projects & deployments, Craig is passionate about developing his skills. He enjoys contributing to a personal blog to give back to the SQL community. In his spare time Craig is an avid runner

View all posts by Craig Porteous

Latest posts by Craig Porteous (see all)

How to secure Reporting Services with Group Managed Service Accounts (GMSA) - November 7, 2018
Contribute, contribute, contribute! - June 20, 2018
Top 10 things you must document in SQL Server Reporting Services (SSRS) - February 26, 2018

RS Scripter

Reporting Services Migration Tool

Rs.exe VB Script

Constant backfill

dbatools PowerShell module

Reporting Services PowerShell module

Migrate all SSRS folders

Restore or backup an Encryption Key

Update Data Sources from a reference table

Conclusion

Restore-RSDatabase

Set-RSFolderSecurity

Reset-RSScaleOut

Remove-RSSubscription

References

Appendix

Related posts: