SQL Server Reporting Services Data Source Deployment Automation with PowerShell

By:   |   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.

Data Source File Folder

The data source file can be read using a text editor such as Notepad or Notepad++ as below.

Data Source File Content

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.

Print Data Source Output

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.

Data Source Deployment

After the successful deployment, the data source property can be seen as shown below:

Data Source Property

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.

Data Source Deployment With Parameters

After successful deployment, the data source property can be seen as shown below:

Data Source Property

Summary

As per the above mentioned solution it is confirmed that Reporting Services data source can be deployed using PowerShell script.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Nat Sundar 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

Comments For This Article




Wednesday, January 18, 2017 - 7:20:28 PM - Dorababu Back To Top (45407)

Hi I am able to access the reporting server and able to create a report too using PowerShell but the only problem is while creating Data Source


Wednesday, January 18, 2017 - 9:29:14 AM - Nat Sundar Back To Top (45398)

 

Hi

I recommend you to try the code in the first tip which is published here.

https://www.mssqltips.com/sqlservertip/4294/accessing-sql-server-reporting-services-data-using-powershell/

This will help you to validate the whether you can access the server first. If you are able to do so, Please you may try the script documented in this tip.

 

 


Tuesday, January 17, 2017 - 6:45:58 AM - Dorababu Back To Top (45367)

 I have the following code which is giving me an exception can you help me

 

 $ssrsproxy = New-WebServiceProxy -Uri $webServiceUrl -UseDefaultCredential -Namespace "SSRS"
    $proxyNameSpace = $ssrsproxy.gettype().Namespace

    #$type = $proxyNameSpace.GetType().Namespace
    $datatype = ($proxyNameSpace + '.DataSourceDefinition')

    $datasourceDef = New-Object($datatype)
   
    #$datasourceDef
   

    $datasourceDef.connectstring = $connectString
    $datasourcedef.Extension = $Extension
    $datasourceDef.Enabled = $true
    $datasourceDef.EnabledSpecified = $true
    $datasourceDef.ImpersonateUserSpecified = $false
    $datasourceDef.Prompt = $null
    $datasourceDef.WindowsCredentials = $false

    try
    {
        $ssrsproxy.CreateDataSource($DataSourceName, $path, $true, $datasourceDef, $null)
    }
    catch
    {
        "Error was $_"
        $line = $_.InvocationInfo.ScriptLineNumber
        "Error was in Line $line"
    }

 

Error I am getting

Error was Cannot convert argument "Definition", with value: "SSRS.DataSourceDefinition", for "CreateDataSource" to type "SSRS.DataSourceDefinition
": "Cannot convert the "SSRS.DataSourceDefinition" value of type "SSRS.DataSourceDefinition" to type "SSRS.DataSourceDefinition"."
Error was in Line 54

 















get free sql tips
agree to terms