Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Implement Continuous Delivery for SQL Server Reporting Service Reports


By:   |   Read Comments (3)   |   Related Tips: > Reporting Services Development

Problem

What is Continuous Delivery and how do I implement Continuous Delivery for SQL Server Reporting Services Reports?

Solution

Continuous Delivery

As per Martin Fowler, "Continuous Delivery is a software development discipline where you build software in such a way that the software can be released to production at any time”.

There are two key elements to implement Continuous Delivery:

  • DevOps Culture
  • Deployment Automation

Continuous Integration vs. Continuous Delivery

CI (Continuous Integration) refers to the process of integrating, building, and testing for development purpose. However CD (Continuous Delivery) will help us to prepare the artifacts for the purpose of production deployment.

Deployment Automation

Deployment automation helps applications to deploy into multiple environments. This solution improves the productivity of both the Development and Operation teams.

Advantages of Deployment Automation

  • Repeatability - Deployment to multiple environments is done using the same process
  • Insight - Deployment related errors can be identified early in the process
  • Low Risk - Completely remove the bottleneck of key resources (Developers) for the deployment

Most of the application release automation tools allow configuring a script to deploy. We have already learned to develop a PowerShell script to deploy the data source in the previous tip. In this tip we will learn to deploy the datasets and the reports using a PowerShell script. This script can be integrated with any release automation tool to enable Continuous Delivery for SQL Server Reporting Service Reports.

Deploying Shared Datasets

As same as the data source, the dataset can also be read as an XML object. This XML object will have the details about the Query, Columns and the data source.  PowerShell can create a dataset object in the Reporting Services proxy with the help of an XML object. The CreateCatalogItem function can be used to create a dataset on the Reporting Services proxy.

Once the dataset has been created, the data source reference has to be updated. The SetItemDataSource function will help us to link the data source for the given dataset.

The script below is used to read and deploy the dataset to a remote Reporting Services Server.

$ReportServerUri = "http://localhost/ReportServer_SQL2012/ReportService2010.asmx?wsdl";
$DataSetFile = "C:\MSSQLTips\Tips\SSRS_Deployment_Using_ps_Part3\Reference\Scripts\AdventureWorks Sample Reports\Currency.rsd";
$DataSourceFolder = "/MSSQLTips/Data Sources";
$DataSetFolder = "/Datasets";

try{
#Create Proxy
$global:proxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential
-ErrorAction Stop;
If ($proxy -ne $null)
{
echo $global:proxy.ToString();
}

#$DataSetFile = Get-Content -Path $DataSetFilePath
$DataSetName = [System.IO.Path]::GetFileNameWithoutExtension($DataSetFile);
$stream = [System.IO.File]::ReadAllBytes( $DataSetFile);
$warnings =@();

#Create dataset item in the server
try{
$newDataSet = $proxy.CreateCatalogItem("DataSet”,$DataSetName,”$DataSetFolder”,
$true,$stream,$null,[ref]$warnings); }catch{ throw $_.Exception; }
[xml]$XmlDataSetDefinition = Get-Content $DataSetFile;
$xmlDataSourceReference = $XmlDataSetDefinition.SharedDataSet.DataSet | where {$_ | get-member Query};

try{ $dataSetDataSources = $proxy.GetItemDataSources(“$($newDataSet.Path)”); }
catch{ throw $_.Exception; }
foreach ($dataSetDataSource in $dataSetDataSources)
{ #Should only be one!
$proxyNamespace = $dataSetDataSource.GetType().Namespace;
$newDataSourceReference = New-Object (“$proxyNamespace.DataSource”);
$newDataSourceReference.Name = $dataSetDataSource.Name;
$newDataSourceReference.Item = New-Object (“$proxyNamespace.DataSourceReference”);
$newDataSourceReference.Item.Reference =
“$DataSourceFolder/$($xmlDataSourceReference.Query.DataSourceReference)”;
$dataSetDataSource.item = $newDataSourceReference.Item;
try { $proxy.SetItemDataSources(“$DataSetFolder/$DataSetName”, $newDataSourceReference);
}
catch{ throw $_.Exception; }
}

echo "Dataset has been deployed !”;
}

catch {
$valProxyError = $_.Exception.Message;
echo $_.Exception.Message;
}

You will get this message (as shown in the image below) after successful execution.

Deploy the dataset to a remote Reporting Services Server

After successful deployment, the data set property can be seen as shown below.

Deployed Dataset named Currency in SQL Server Reporting Services

Deploying Reports

Reporting Services Reports can also be deployed the in same way as the data set. First the report will be created using the CreateCatalogItem function. If there are no shared datasets used in the report, then the report needs to be re-linked with the data sources.

The SetItemDataSource function will help us to link the data source for the given report.

The script below is used to read and deploy the data set to a remote Reporting Services Server.

$ReportServerUri = "http://localhost/ReportServer_SQL2012/ReportService2010.asmx?wsdl";
$ReportFile = "C:\MSSQLTips\Tips\SSRS_Deployment_Using_ps_Part3\Reference\ScriptsAdventureWorks Sample Reports\Employee Sales Summary SQL2008R2.rdl";
$DataSourceFolder = "/MSSQLTips/Data Sources";
$DataSetFolder = "/MSSQLTips/Datasets";
$ReportsFolder = "/MSSQLTips/Reports";
$reportDataSource = "AdventureWorks2008R2";

try{
#Create Proxy
$global:proxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential -ErrorAction Stop;
If ($proxy -ne $null)
{
echo $global:proxy.ToString()
}

$ReportName = [System.IO.Path]::GetFileNameWithoutExtension($ReportFile)
$stream = [System.IO.File]::ReadAllBytes( $ReportFile);
$warnings =@();

try{ $newReport = $proxy.CreateCatalogItem(“Report”,$ReportName,”$ReportsFolder”,$true,$stream,$null,[ref]$warnings);
 }catch{ throw $_.Exception; }
#relink report to datasource
echo “Updating Datasource references”;
try{ $reportDataSources = $proxy.GetItemDataSources(“$ReportsFolder/$ReportName”); }catch{ throw $_.Exception; }
foreach ($reportDataSource in $reportDataSources)
{
$serverDataSourceItem = $allitems | where {($_.TypeName -eq “DataSource”) -and ($_.Path -eq 
      “$DataSourceFolder/$($reportDataSource.Name)”)};
$proxyNamespace = $reportDataSource.GetType().Namespace;
$newDataSourceReference = New-Object (“$proxyNamespace.DataSource”);
$newDataSourceReference.Name = $reportDatasource.Name;
$newDataSourceReference.Item = New-Object (“$proxyNamespace.DataSourceReference”);
#$newDataSourceReference.Item.Reference = $serverDataSourceItem.Path;
$newDataSourceReference.Item.Reference = "$DataSourceFolder/$($reportDataSource.Name)";
$reportDataSource.item = $newDataSourceReference.Item;
try{ $proxy.SetItemDataSources(“$ReportsFolder/$ReportName”, $newDataSourceReference); }catch{ throw $_.Exception; }
}

echo “Report has been deployed !”;

}
catch {
$valProxyError = $_.Exception.Message;
echo $_.Exception.Message;
}

You will get this message (as shown in the image below) after successful execution.

Deploy the data set to a remote Reporting Services Server

After successful deployment, the dataset property can be seen as shown below

The dataset property in SQL Server Reporting Services

Deploying the Reporting Services Data Source, Dataset and Reports in Folder

A typical Business Intelligence (BI) solution will have many reports, datasets and more than one data source. It is expected to deploy all the items as a part of the deployment automation.

The below PowerShell script will help us deploy all the available files for a Reporting Services Report.

The get-ChildItem cmdlet has been used to list the available files in the given folder. The output of the cmdlet has been filtered (for Dataset, Data source or reports) using the file extension. These files have been passed to the deployment function using the pipeline operator (|).

The deploy function processes each file using a foreach loop and deploys into a Reporting Services server.

The below script can be used to deploy multiple Data sources, Datasets and Reports available in a given folder.

function DeployAllItems()
{
echo “Refreshing reports”
echo “LocalReportsPath: $LocalReportsPath”;
echo “ReportServerUri: $ReportServerUri”;
echo “DataSource Folder: $DataSourceFolder”;
echo “DataSet Folder: $DataSetFolder”;
echo “Reports Folder: $ReportsFolder”;
echo “DataSource UserName: $DataSourceUserName”;
#Create SSRS Data Source , Dataset and Report Folders
#Create-SSRS-Report-Folders;
get-childitem $LocalReportsPath *.rds | DeployDataSources;
get-childitem $LocalReportsPath *.rsd | DeployDataSet;
get-childitem $LocalReportsPath *.rdl | DeployReports;
}

function DeployDataSources()
{
echo “Refreshing Datasources.”;
#Create SSRS Reports Folder
try{ $allitems = $proxy.ListChildren(“/”,$true); }catch{ throw $_.Exception; }

foreach ($o in $input)
{
$dataSourceInfo = $proxy.GetItemType(“$DataSourceFolder/$($o.BaseName)”);

echo “Creating DataSource $DataSourceFolder/$($o.BaseName)…”;


[xml]$XmlDataSourceDefinition = Get-Content $o.FullName;
$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;
$dataSourceDefinition.ConnectString = "Data Source=.\SQL2012;Initial Catalog=AdventureWorks2012";

$credentialRetrievalDataType = ($type + ‘.CredentialRetrievalEnum’);
$credentialRetrieval = new-object ($credentialRetrievalDataType);
$credentialRetrieval.value__ = 1;# Stored
#$dataSourceDefinition.WindowsIntegratedSecurity = $true

$dataSourceDefinition.CredentialRetrieval = "Integrated";
#$dataSourceDefinition.WindowsCredentials = $true;
#$dataSourceDefinition.UserName = $DataSourceUserName;
#$dataSourceDefinition.Password = $DataSourcePassword;

try{ $newDataSource = $proxy.CreateDataSource($xmlDataSourceName.Name,$DataSourceFolder,$true,$dataSourceDefinition,$null);
    }catch{ throw $_.Exception; }
echo “Done.”;
}
echo “Finished.”;
}

function DeployDataSet()
{
echo “Refreshing DataSets.”;
try{ $allitems = $proxy.ListChildren(“/”,$true); }catch{ throw $_.Exception; }

foreach ($o in $input)
{
$dataSetInfo = $proxy.GetItemType(“$DataSetFolder/$($o.BaseName)”);

echo “Creating DataSet $DataSetFolder/$($o.BaseName)…”;
$stream = [System.IO.File]::ReadAllBytes( $($o.FullName));
$warnings =@();

#Create dataset item in the server
try{ $newDataSet = $proxy.CreateCatalogItem(“DataSet”,”$($o.BaseName)”,”$DataSetFolder”,$true,$stream,$null,[ref]$warnings);
     }catch{ throw $_.Exception; }
#relink dataset to datasource

echo “Updating Datasource reference”;
[xml]$XmlDataSetDefinition = Get-Content $o.FullName;
$xmlDataSourceReference = $XmlDataSetDefinition.SharedDataSet.DataSet | where {$_ | get-member Query};

try{ $dataSetDataSources = $proxy.GetItemDataSources(“$($newDataSet.Path)”); }catch{ throw $_.Exception; }
foreach ($dataSetDataSource in $dataSetDataSources)
{ #Should only be one!
$proxyNamespace = $dataSetDataSource.GetType().Namespace;
$newDataSourceReference = New-Object (“$proxyNamespace.DataSource”);
$newDataSourceReference.Name = $dataSetDataSource.Name;
$newDataSourceReference.Item = New-Object (“$proxyNamespace.DataSourceReference”);
$newDataSourceReference.Item.Reference = “$DataSourceFolder/$($xmlDataSourceReference.Query.DataSourceReference)”;
$dataSetDataSource.item = $newDataSourceReference.Item;
try { $proxy.SetItemDataSources(“$DataSetFolder/$($o.BaseName)”, $newDataSourceReference); }catch{ throw $_.Exception; }
}
echo “Done.”;
}
echo “Finished refreshing DataSets.”;
}

function DeployReports()
{
echo “Refreshing Reports.”;
try{ $allitems = $proxy.ListChildren(“/”,$true); }catch{ throw $_.Exception; }
$folderInfo = $proxy.GetItemType(“$ReportsFolder”);

# Iterate each report file
foreach ($o in $input)
{
#echo “Checking report $Folder/$($o.BaseName) exists on server.”;
try{ $reportInfo = $proxy.GetItemType(“$ReportsFolder/$($o.BaseName)”); }catch{ throw $_.Exception; }
echo “Creating report $ReportsFolder/$($o.BaseName)…”;
$stream = [System.IO.File]::ReadAllBytes( $($o.FullName));
$warnings =@();
try{ $newReport = $proxy.CreateCatalogItem(“Report”,”$($o.BaseName)”,”$ReportsFolder”,$true,$stream,$null,[ref]$warnings);
     }catch{ throw $_.Exception; }

#relink report to datasource
echo “Updating Datasource references”;
try{ $reportDataSources = $proxy.GetItemDataSources(“$ReportsFolder/$($o.BaseName)”); }catch{ throw $_.Exception; }
foreach ($reportDataSource in $reportDataSources)
{
$serverDataSourceItem = $allitems | where {($_.TypeName -eq “DataSource”) -and ($_.Path -eq 
     “$DataSourceFolder/$($reportDataSource.Name)”)};
$proxyNamespace = $reportDataSource.GetType().Namespace;
$newDataSourceReference = New-Object (“$proxyNamespace.DataSource”);
$newDataSourceReference.Name = $reportDatasource.Name;
$newDataSourceReference.Item = New-Object (“$proxyNamespace.DataSourceReference”);
$newDataSourceReference.Item.Reference = $serverDataSourceItem.Path;
$reportDataSource.item = $newDataSourceReference.Item;
try{ $proxy.SetItemDataSources(“$ReportsFolder/$($o.BaseName)”, $newDataSourceReference); }catch{ throw $_.Exception; }
}

#relink report to shared datasets
echo “Updating DataSet references”;
[xml]$XmlReportDefinition = Get-Content $o.FullName;
if ($XmlReportDefinition.Report.DataSets.Dataset.count > 0)
{
$SharedDataSets = $XmlReportDefinition.Report.DataSets.Dataset | where {$_ | get-member SharedDataSet};
$DataSetReferences = @();
try{ $reportDataSetReferences = $proxy.GetItemReferences(“$ReportsFolder/$($o.BaseName)”, “DataSet”) | 
     where {$_.Reference -eq $null}; }catch{ throw $_.Exception; }
$newDataSetReferences = @();
foreach ($reportDataSetReference in $reportDataSetReferences)
{
$serverDataSetReference = $allitems | where {($_.TypeName -eq “DataSet”) -and 
    ($_.Path -eq “$DataSetFolder/$($reportDataSetReference.Name)”)};
$proxynamespace =$reportDataSetReference.Gettype().NameSpace;
$newDataSetReference = New-Object (“$proxyNamespace.ItemReference”);
$newDataSetReference.Name = $serverDataSetReference.Name;
$newDataSetReference.Reference = $serverDataSetReference.Path;
$newDataSetReferences += $newDataSetReference;
}
try{ $DataSetReferences += $proxy.SetItemReferences(“$ReportsFolder/$($o.BaseName)”, $newDataSetReferences);
    }catch{ throw $_.Exception; }

}
echo “Applying…”;
try{ $proxy.SetPolicies(“$ReportsFolder/$($o.BaseName)”,$newPolicies); }catch{ throw $_.Exception; }
echo “Done.”;
}
echo “Finished refreshing Reports.”;
}

#Entry Point & Globals
$LocalReportsPath = "C:\MSSQLTips\Tips\SSRS_Deployment_Using_ps_Part3\Reference\Scripts\AdventureWorks Sample Reports";
$ReportServerUri = "http://localhost/ReportServer_SQL2012/ReportService2010.asmx?wsdl";
$ReportsParentFolder = "/";
$SSRSReportsFolderName = "MSSQLTips";

$DataSourceFolderName = “Data Sources”;
$DataSetFolderName = “Datasets”;
$ReportsFolderName = “Reports”;

$SSRSReportsFolder = $ReportsParentFolder + $SSRSReportsFolderName
$DataSourceFolder = $SSRSReportsFolder + “/” + $DataSourceFolderName ;
$DataSetFolder = $SSRSReportsFolder + “/” + $DataSetFolderName ;
$ReportsFolder = $SSRSReportsFolder + “/” + $ReportsFolderName ;


try{
#Create Proxy
$global:proxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential -ErrorAction Stop;
$valReportsServerUri = ($proxy -ne $null);
}
catch {
$valProxyError = $_.Exception.Message;
}

DeployAllItems;
echo “Done.”

The execution result of the PowerShell script can be seen below.

Deploy multiple Data sources, Datasets and Reports available in a given folder

The below picture highlights the deployed reports.

Deployed Data sources, Datasets and Reports in SQL Server Reporting Services

Summary

The process of automating the deployment for application development has matured, however deploying Reporting Services reports still has a few challenges to overcome.

The best way to go forward is to automate the deployment using the scripts in a development environment. You may need to tweak the script to make it work for different scenarios.

Once the deployment process has matured, try to deploy the code to a Test and Pre-Production environments.

Next Steps


Last Update:






About the author
MSSQLTips author Nat Sundar Nat Sundar is working as an independent SQL BI consultant in the UK with Bachelor Degree in Engineering.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Wednesday, November 23, 2016 - 7:09:40 AM - Balaban Mihai Back To Top

Hi Nat!

Thank you very much for the fast response and the very nice solution!

 

Have a great day,

Mihai


Tuesday, November 22, 2016 - 11:49:42 AM - Nat Sundar Back To Top

Hi Mihai

Thanks for your comments.

This has been explained in detail in the previous tip. Pls refer below.

https://www.mssqltips.com/sqlservertip/4429/sql-server-reporting-services-data-source-deployment-automation-with-powershell/

Thanks

Nat

 

 


Tuesday, November 22, 2016 - 3:28:02 AM - Balaban Mihai Back To Top

 

 Hi, 

Very nice script but you are setting your connection string manually ""Data Source=.\SQL2012;Initial Catalog=AdventureWorks2012";"

How can you do it automatically? Read from the local file datasource connection string?

Thank you,

Mihai

 

 


Learn more about SQL Server tools