One of the most demanding and long awaiting feature in SSRS is “Automation of Deployment”. So in this blog I am going to share simple steps to completely automate the deployment and will share the tested vb.net code, but before getting into the automated script let us explore the available options
- BIDS Development environment(or SQL data tools in 2012) can deploy your packages to the desired server
- Very easy to configure
- Not practical for production rollout, as most companies follows segregation of duties. So developer won’t have production access and DBA shouldn’t open the package in development tools.
- Most often you want to deploy only one or two changes in the specific report rather than the whole package
- Always deploy the latest version of code. We can’t specify the version or Branching in TFS/subversion.
- Report Manager
- Can be used by DBA with some knowledge in SSRS
- Can deploy specific objects
- Completely manual and mistake can easily happen
- Some organisation don’t have dedicated DBAs and they don’t have much knowledge about the SSRS
- Deployment instruction will be very lengthy and will consume significant amount of developer’s time incase of multiple reports and shared dataset
- RS.exe ( via web service) – 2005 name space
- Deployment can be automated (partially)
- Famous RSscripter (was written by Jasper Smith) is developed based on 2005 namespace and was very helpful to produce automated scripts
- The scripts are very tedious to edit and develop.
- There is no build tool available from Microsoft
- 2005 namespace doesn’t support linking of items(e.g. report to dataset and so on)
- RS.Exe (via web service) – 2010 Name space
- Deployment can be fully automated include linking of items
- No tools available in the market to automate the deployment
- RSscripter doesn’t get it’s update for 2010 namespace
So the best option is RS.exe with 2010 namespace. I was searching for the automated script and found the article from John Desch in the Microsoft blog Click here
it is one of the best automated script available in the market now as it uses the latest namespace. But it has it own limitations so in the blog i have expanded the functionality and given the revised code and steps to automate your deployment.
- The code doesn't work with 2008Sp1 and above. It requires the buffer error fix.
- The code tries to link all the reports in the deployed report folder from the servers to their Data source. This will create a problem, if you want to deploy only two reports in the existing 8 report pack. So created an additional method (UpdateDataSources_report) to link only the deployed report into their respective sources
- The code Doesn't link the Shared data set to Report. so created an additional method (UpdateDataSet_report) which will automatically link your report to their respective datasets
- The existing code doesn't overwrite the files if they exist. changed the flags (for dataset and report only) as the deployment usually requires overwriting the existing objects.
- At last it would be easy if you have a single deployment script with command prompt variable so that you can pass to DBA. SO created a deploy.bat with variables
- You can download the code and batch file from my skydrive (or one drive )
- Save both file in same folder in your local drive
- Create a folder named “Reports” under this new folder
- Now copy all the reports, datasource and dataset into the “Reports” folder
- Now Deploy.bat has the below variables amend them accordingly
varServerPath=http://local/reportserver ---- Desired Server URL
set varReportFolder=Test/Testdeploy ---- Server folder path --from the main
Set varDatasetFolder=Datasets ---- Dataset folder path
set varDataSourceFolder=Data Sources ---- Datasource path
Set varDataSourcePath=Data Sources ---- Datasource path again (Yes this is Redundant)
set varReportName= -----If you want to restrict the script to one report then mention it’s name without
extension else leave it blank
set varReportFilePath=%\Reports ---- Local folder path where you stored your reports and dependant objects.
Now, you can zip the folder and send it to DBA (Note: please test the script in your development server first)
- Can I refer two different datasource folder for different dataset?
- NO, you need to declare a collection object and modify the code. This is very rare occasion.
- Report is not able to link my dataset.
- Check the name in your report. Shared dataset should be creaed with same name
- Can I deploy report in multiple folder path
- Yes, you need divide them into multiple deployment package
Revised Code (save the script as Commonscript.rss)