By: Nat Sundar | Updated: 2016-08-31 | Comments (3) | Related: > Reporting Services Administration
Problem
I read about accessing SQL Server Reporting Service using PowerShell in this tip, but how do I deploy the Data Source to a remote Reporting Services Server? Is this possible with PowerShell?
Solution
Since the Reporting Service data source is an XML file, it can be processed as a XML document object. This data source XML object can be deployed or created in the Reporting Service proxy.
Reading Data Source File
The Data source file content can be imported as an XML object. This will help us to collect the properties such as Data Source Name, Adapter Details and Connection String.
The picture below shows the presence of a data source file for the AdventureWorks database.
The data source file can be read using a text editor such as Notepad or Notepad++ as below.
PowerShell has a facility to navigate the XML document with ease. This is achieved by using dot notation which is easy to understand. We will be able to access the attributes and the child nodes using this approach. The Get-Content cmdlet can be used to read a XML document.
In the below script, the output of the Get-Content cmdlet will be stored in a XML variable. This XML variable has been used to derive the data source details like the name and the connection string.
$DataSourceFile = "C:\Reference\Scripts\AdventureWorks2008R2.rds"
[xml]$XmlDataSourceDefinition = Get-Content $DataSourceFile;
Echo("Data Source Name:$($XmlDataSourceDefinition.RptDataSource.Name)")
$xmlDataSourceName = $XmlDataSourceDefinition.RptDataSource | where {$_ | get-member ConnectionProperties};
Echo ("Data Source Extension:$($xmlDataSourceName.ConnectionProperties.Extension)")
Echo ("Data Source Connection String:$($xmlDataSourceName.ConnectionProperties.ConnectString)")
Echo ("Data Source Integrated Security:$($xmlDataSourceName.ConnectionProperties.IntegratedSecurity)")
The execution result of the PowerShell script can be seen below.
Deploying the Reporting Services Data Source
The deployment is achieved by creating a data source definition object in the Reporting Services proxy.
The following attributes are mandatory to create the data source definition:
- Data Source Name - Name of the data source
- Data source Definition Type - Derived from the proxy namespace
- Connection String and Extension - Derived from the data source file
- Credential - Derived / Integrated Security
The script below is used to read and deploy the data source to a remote Reporting Services Server.
$DataSourceFile = "C:\Reference\Scripts\AdventureWorks2008R2.rds"
$ReportServerUri = "http://localhost/ReportServer_SQL2012/ReportService2010.asmx?wsdl"
$DataSourceFolder = "/MSSQLTips"
try{
#Create Proxy
$global:proxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential -ErrorAction Stop;
If ($proxy -ne $null)
{
echo $global:proxy.ToString()
}
}
catch {
$valProxyError = $_.Exception.Message;
echo $_.Exception.Message;
}
[xml]$XmlDataSourceDefinition = Get-Content $DataSourceFile;
Echo("Data Source Name:$($XmlDataSourceDefinition.RptDataSource.Name)")
$xmlDataSourceName = $XmlDataSourceDefinition.RptDataSource | where {$_ | get-member ConnectionProperties};
Echo ("Data Source Extension:$($xmlDataSourceName.ConnectionProperties.Extension)")
Echo ("Data Source Connection String:$($xmlDataSourceName.ConnectionProperties.ConnectString)")
Echo ("Data Source Integrated Security:$($xmlDataSourceName.ConnectionProperties.IntegratedSecurity)")
try{ $type = $proxy.GetType().Namespace; }catch{ throw $_.Exception; }
$dataSourceDefinitionType = ($type + '.DataSourceDefinition');
$dataSourceDefinition = new-object ($dataSourceDefinitionType);
$dataSourceDefinition.Extension = $xmlDataSourceName.ConnectionProperties.Extension; #SQL
$dataSourceDefinition.ConnectString = $xmlDataSourceName.ConnectionProperties.ConnectString
$dataSourceDefinition.CredentialRetrieval = 'Integrated' #$credentialRetrieval;
try{ $newDataSource = $proxy.CreateDataSource($xmlDataSourceName.Name,
$DataSourceFolder,$true,$dataSourceDefinition,$null); }catch{ throw $_.Exception; }
echo "Done.";
You will get this message (as mentioned in the image below) after the successful execution.
After the successful deployment, the data source property can be seen as shown below:
Passing Parameters to the Deployment Script
As we need to deploy the same data source to multiple servers (Dev, Test and Production), it becomes necessary to change the connection string and credentials details with every deployment. The script has been modified to accept following parameters:
- DataSourceFile - Location of the Data Source File
- ReportServerUri - Reporting Services URI
- DataSourceFolder - Target reporting service folder
- DBServerName - Database Server Name
- DatabaseName - Database Name
- DataSourceUserName - Service account name (to access DB Server)
- DataSourcePassword - Service account password
Let's reuse the same script with some enhancements.
Param(
[Parameter(Mandatory=$True,Position=1)]
[string]$DataSourceFile,
[Parameter(Mandatory=$True,Position=2)]
[string]$ReportServerUri,
[Parameter(Mandatory=$True,Position=3)]
[string]$DataSourceFolder,
[Parameter(Mandatory=$True,Position=4)]
[string]$DBServerName,
[Parameter(Mandatory=$True,Position=5)]
[string]$DatabaseName,
[Parameter(Mandatory=$True,Position=6)]
[string]$DataSourceUserName,
[Parameter(Mandatory=$True,Position=7)]
[string]$DataSourcePassword
)
Echo "Data Source File: $DataSourceFile"
Echo "Report Server URI: $ReportServerUri"
Echo "Data Source Folder: $DataSourceFolder"
Echo "DB Server Name: $DBServerName"
Echo "Database Name: $DatabaseName"
Echo "Data Source UserName: $DataSourceUserName"
$ConnectString = "Data Source="+ $DBServerName+ ";Initial Catalog="+ $DatabaseName
try{
#Create Proxy
$global:proxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential -ErrorAction Stop;
If ($proxy -ne $null)
{
echo $global:proxy.ToString()
}
}
catch {
$valProxyError = $_.Exception.Message;
echo $_.Exception.Message;
}
[xml]$XmlDataSourceDefinition = Get-Content $DataSourceFile;
#Echo("Data Source Name:$($XmlDataSourceDefinition.RptDataSource.Name)")
$xmlDataSourceName = $XmlDataSourceDefinition.RptDataSource | where {$_ | get-member ConnectionProperties};
try{ $type = $proxy.GetType().Namespace; }catch{ throw $_.Exception; }
$dataSourceDefinitionType = ($type + '.DataSourceDefinition');
$dataSourceDefinition = new-object ($dataSourceDefinitionType);
$dataSourceDefinition.Extension = $xmlDataSourceName.ConnectionProperties.Extension; #SQL
$dataSourceDefinition.ConnectString = $ConnectString
$credentialRetrievalDataType = ($type + '.CredentialRetrievalEnum');
$credentialRetrieval = new-object ($credentialRetrievalDataType);
$credentialRetrieval.value__ = 1;# Stored
$dataSourceDefinition.CredentialRetrieval = $credentialRetrieval;
$dataSourceDefinition.WindowsCredentials = $true;
$dataSourceDefinition.UserName = $DataSourceUserName;
$dataSourceDefinition.Password = $DataSourcePassword;
try{ $newDataSource = $proxy.CreateDataSource($xmlDataSourceName.Name,$DataSourceFolder,$true,
$dataSourceDefinition,$null); }catch{ throw $_.Exception; }
echo "Done.";
Now the PS script need to be executed with the parameters
.\Deploy_DataSource_With_Parameters.ps1 "C:\Reference\Scripts\AdventureWorks2008R2.rds" "http://localhost/ReportServer_SQL2012/ReportService2010.asmx?wsdl" "/MSSQLTips" "localhost\SQL2012" "AdventureWorks2012" "Domain\UserName" "Password"
The script will deploy the data source based on the supplied parameters.
After successful deployment, the data source property can be seen as shown below:
Summary
As per the above mentioned solution it is confirmed that Reporting Services data source can be deployed using PowerShell script.
Next Steps
- Stay tuned to deploy Datasets in the next tip.
- Read about SSRS and PowerShell scripts here.
- Read about PowerShell cmdlets for SSRS SharePoint mode here.
- Check out these MSSQLTips.com resources:
About the author
Nat Sundar is working as an independent SQL BI consultant in the UK with a Bachelors Degree in Engineering.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2016-08-31
