- Using the report manager directly on http page on the report server. Well documented here. Works fine if you have a very few reports to deploy on a very few SSRS instances.
- Using the rs.exe tool (should be here C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn). Documented on MSDN. For some reason this tool does not work on my company desktops (got an authentication error). Works fine if you does not need to alter the datasource of the report at deployment-time.
- But if your need is a bit more specific (like changing the datasource when you deploy your report, or quickly deploy many reports on many servers/folders), you will end up with custom code which uses Reporting Services web service. You may find plenty of solutions on the web which use this web service to deploy reports, but I found out this interesting one which points to the essential of the web service usage.
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 |
- 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.- /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.
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.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 |
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.
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 managerhttp://dobrzanski.net/2008/08/12/reporting-services-deploying-rdl-files/
http://msdn.microsoft.com/en-us/library/ms162839(v=SQL.100).aspx
http://blog.magenic.com/blogs/daniels/archive/2008/11/25/Deploying-SSRS-Reports-from-Powershell.aspx
http://wmug.co.uk/blogs/sean/archive/2009/04/12/adding-ssrs-2008-web-reference-in-visual-studio-2008.aspx
http://devlicio.us/blogs/derik_whittaker/archive/2008/07/21/referencing-2-0-web-services-asmx-in-visual-studio-2008.aspx
http://www.codeproject.com/KB/recipes/commandlineparser.aspx
Hello Silvère LHERMITE. Thanks for the article. The solution, however, can't be found. Could you fix the http://www.filehosting.org/file/details/300719/ReportingServicesDeployer.zip link? Thank you.
ReplyDeleteHi Unknown!
DeleteOops it seems I was using a bad solution for long-time storage!
I've just uploaded the archive here, so let me know if you experience any trouble with getting it:
http://slhermite.netii.net/blog/ReportingServicesDeployer.zip