Have you ever tried to bulk-deploy reporting services 2008 files ? After some Internet searches, you may find the following ways :
What does the tool do
In my specific context, there is a dozen of different reports, each report being deployed on a dozen of folders. Each folder has its own datasource, so I need to update the datasource of each report depending on the folder it is being deployed. And finally, the deployment has to be fully automated because it has to be included in a software factory process.
 |
| Folder structure once deployed on Reporting Services instance |
I suggest a small console application which performs the following operations:
- Read on command line the list of report files (rdl) to be deployed, the target SSRS url, the connection string to use in datasource, the target SSRS folder, and the friendly name of the report once deployed.
- Open the web service located on SSRS url (from command line).
- Through web service, ensure the target folder exists, else create it.
- Through web service, create a dedicated datasource inside of the target folder, pointing to the connection string provided in command line.
- For each report to be deployed, use web service to deploy it, and then alter the datasource it is using.
- Displays an error on standard console output and returns a non-zero value if something goes wrong.
Command line parsing and usage (step 1)
As usual, I adapted the code from
Ray Hayes, which is very easy to use.
The allowed options are:
- /H /Help => Optional, displays a usage message and returns
- /S /WebServiceUrl => Mandatory, the URL used to deploy the SSRS reports. For instance: http://localhost/ReportServer/ReportService2005.asmx
- /C /ConnectionString => Mandatory, the connection string used by SSRS reports to connect to the SQL database, without credentials info. For instance: Data Source=YourSqlHostName\YourSqlInstance;Initial Catalog=YourDb;
- /L /LoginSql => Mandatory, the login used by SSRS reports to connect to the SQL database. It cannot be part of the previous connection string, must be set by /L.
- /P /PasswordSql => Mandatory, the password to the previous login.
- /F /TargetFolder => Optional, the SSRS folder in which to deploy the reports. If not supplied, reports will be created in root folder.
- File1, File2, ... => List of report files to be deployed. If a directory is provided instead of file, all the *.rdl file in it will be deployed, ignoring backup files and sub-directories.
Usually I create one cmd file for each folder I want to update.
SET DEPLOYER="ReportingServicesDeployer.exe"
SET WSDL_URL="http://_YourSSRSServer_/ReportServer/ReportService2005.asmx"
SET RS_FOLDER="TopFolder/SubFolder"
SET DS_CS="Data Source=_YourSqlHostname_;Initial Catalog=_YourSqlDb_;"
SET DS_USER="_YourSqlLogin_"
SET DS_PWD="_YourSqlPassword_"
SET CONTENT_DIR="C:\YourReportSolution\YourReportProject"
%DEPLOYER% /S %WSDL_URL% /F %RS_FOLDER% /C %DS_CS% /L %DS_USER% /P %DS_PWD% %CONTENT_DIR%
Dynamic web service reference to ReportService2005.asmx (step 2)
One of the funny part of this tool is to connect to a web service URL provided on the command line. There are some tips to know in Visual Studio 2008 to achieve this without headache. This is clearly explained
here.
At first try, I used the following url: http://localhost/reportserver/reportservice.asmx?wsdl But it did not work at all, so I tried http://localhost/reportserver/reportservice
2005.asmx?wsdl and it worked fine (even though my RS version is 2008). Note that http://localhost/reportserver/reportservice
2008.asmx?wsdl does not exist. Could not find a logical explanation about this...
The hot point is to provide the URL of the SSRS interface at runtime. In the VS project, I named the RS web service ReportingService2005. Then I extended this class with the following specific constructor which allows to instantiate the webservice with an URL at runtime:
public partial class ReportingService2005 : System.Web.Services.Protocols.SoapHttpClientProtocol
{
public ReportingService2005(string Url)
{
this.Url = Url;
if ((this.IsLocalFileSystemWebService(this.Url) == true))
{
this.UseDefaultCredentials = true;
this.useDefaultCredentialsSetExplicitly = false;
}
else
{
this.useDefaultCredentialsSetExplicitly = true;
}
}
}
RS folder creation (step 3)
Reporting Services handles a folder system, like standard file system: the root folder is called Home, you can create several folders in a parent one, but each parent has only one parent. When you deploy a report (or a datasource) you have to choose the folder in which it will be done.
 |
| Folders in Reporting Services |
The following snippets creates a folders tree in the Reporting Services file system. It iterates starting from top-level folder down to leaf folder, and for each level it creates the desired folder if it does not already exist.
public static void CreateFolder(ReportingService2005 rs, string Folder)
{
string CurrentFolder = "/";
if (!string.IsNullOrEmpty(Folder))
{
foreach (string NextFolder in Folder.Split(new char[] { '/' }))
if (!string.IsNullOrEmpty(NextFolder))
{
if (rs.GetItemType(string.Concat(CurrentFolder.TrimEnd('/'), "/", NextFolder)) != ItemTypeEnum.Folder)
{
rs.CreateFolder(NextFolder, CurrentFolder, null);
}
CurrentFolder = string.Concat(CurrentFolder.TrimEnd('/'), "/", NextFolder);
}
}
}
RS data source creation (step 4)
First thing to know about data sources in reporting services: they are elements you can deploy on a server, it works the same way you deploy reports. A data source
file is a stand alone element which contains the full connection string to the sql database you are pointing to (server name, instance name, database, login, password, etc.). When you create a data source file, you can choose the folder in which it will be created.
Data sources are used by reports to get the connection string they have to use to fetch data. The idea is that several reports may use the same connection string, so that it is better to store the connection string only once, instead of duplicating it in every reports.
In this tool, all the reports to be deployed will be altered to point to an unique data source. This tool does not support reports having several datasources.
The following code creates a datasource in a given folder. This version allows only sql-login, but it is few modifications to handle another authentication mode.
public static void CreateDataSource(ReportingService2005 rs, string Folder, string DatasourceName, string ConnectionString, string Login, string Password)
{
DataSourceDefinition dsd = new DataSourceDefinition();
dsd.ConnectString = ConnectionString;
dsd.Enabled = true;
dsd.Extension = "SQL";
dsd.CredentialRetrieval = CredentialRetrievalEnum.Store;
dsd.UserName = Login;
dsd.Password = Password;
rs.CreateDataSource(DatasourceName, Folder, true, dsd, null);
}
RS report deployment (step 5)
Deployment of a report is handled by one single method. So if you have 5 five reports to deploy, you have to call this method 5 times. It assumes the web service instance (first parameter of the method) has been previously initialized (step 2).
Even if there is nothing complicated in it, the method is a bit long, so we will cut it in four parts and explain them separately. As you can notice, I took heavy inspiration from
Dan Sniderman's blog.
First part is about reading the report file (rdl extension) from filesystem and storing it in a in-memory buffer for later use. Yes, in-memory storing is evil... but I assume you do not make report which are bigger than 100 MB :)
public static void DeployOneReport(ReportingService2005 rs, string rdlPath, string parentFolder, string reportName, string DatasourceName)
{
Byte[] definition = null;
Warning[] warns = null;
//read the file as a filestream into a byte array
FileStream stream = File.OpenRead(rdlPath);
definition = new Byte[stream.Length];
stream.Read(definition, 0, (int)stream.Length);
Through web service instance, the
CreateReport method allows to deploy the report onto the server. The parameters are the following:
- Report : name of the report in the RS filesystem
- Parent : full path to the RS folder in which you want to store the report
- Overwrite : replace an existing report with the same name?
- Definition : the binary content of the report
- Properties : a list of key-value options
- Returns a list of warnings that could happen during the deployment.
// Call the web service to create the report
rs.Timeout = 1000000;
warns = rs.CreateReport(reportName, parentFolder, true, definition, null);
Then, just do a check on warnings that may have been raised during deployment. I do not care about the shared data source warning, so I explicitly ignore it.
if (warns != null)
{
foreach (Warning warning in warns)
{
//ignore the warning about shared data source
if (!(warning.Message.StartsWith("The dataset ‘") && warning.Message.Contains("’ refers to the shared data source ‘") && warning.Message.EndsWith("which is not published on the report server.")))
{
// otherwise throw it
throw new Exception(string.Format("Warning returned from WebService: {0}", warning.Message));
}
}
}
Final piece of code is about altering the report datasource. Remember that the shared data source used by the original report has not been deployed, and that is exactly what we want! The idea is to make the online report use the data source we deployed in step 4.
To achieve this, we will do the following:
- find the data sources referenced by the online report (see line 1)
- create a DataSourceReference object pointing to online data source from step 4 (see line 4)
- create a DataSource object which includes the previous DataSourceReference and name it just like the first data source from the online report (see lines 6 to 8)
- update the data sources of online report with the DataSource object we just created (see line 9)
DataSource[] ExistingDS = rs.GetItemDataSources(string.Concat(parentFolder, "/", reportName));
if ((ExistingDS != null) && (ExistingDS.Length > 0))
{
DataSourceReference dsr = new DataSourceReference();
dsr.Reference = string.Concat(parentFolder, "/", DatasourceName);
DataSource ds = new DataSource();
ds.Item = dsr;
ds.Name = ExistingDS[0].Name;
rs.SetItemDataSources(string.Concat(parentFolder, "/", reportName), new DataSource[] { ds });
}
}
Download the source code
You can download it
here.
It is a VS2010 solution, you can use the source code the way you want, but it comes without any warranty, etc.
References
Deploy reports using the report manager
http://dobrzanski.net/2008/08/12/reporting-services-deploying-rdl-files/
Deploy reports using the rs.exe utility
http://msdn.microsoft.com/en-us/library/ms162839(v=SQL.100).aspx
Basics of the Reporting Services web service usage
http://blog.magenic.com/blogs/daniels/archive/2008/11/25/Deploying-SSRS-Reports-from-Powershell.aspx
reportservice.asmx vs reportservice
2005.asmx
http://wmug.co.uk/blogs/sean/archive/2009/04/12/adding-ssrs-2008-web-reference-in-visual-studio-2008.aspx
Referencing a dynamic webservice in VS2008
http://devlicio.us/blogs/derik_whittaker/archive/2008/07/21/referencing-2-0-web-services-asmx-in-visual-studio-2008.aspx
Command line parsing
http://www.codeproject.com/KB/recipes/commandlineparser.aspx
