Deploying a SQL Server Reporting Services 2005 report via an MSI
Most of the time, it’s acceptable for customers to deploy a report manually via Visual Studio, Report Manager, or maybe by using an rss script.
Last month I bumped into an organization that had a hard and fast rule that “anything deployable” (Winform apps, Webform apps, SSRS reports, etc.) had to be deployed via an MSI.
So, I put together a little proof of concept showing them how. The sample basically displays some “setup ui” to collect information like where the data source for the report should be created and what it’s name should be, and the username and password that should be used in the data source. Making all this information “dynamic” was important because one often publishes a report and its data source to a different location on a production box than on the machine it was originally created on...your database server name (and even the name of the database) might be different, too
The setup UI runs in the context of a VS deployment package. Once the setup information has been collected, the deployment package launches a custom action. The custom action is nothing more than code which leverages the Reporting Services SOAP APIs to publish the report and data source.
After you have downloaded and unzipped the files, open the PublishReportMSI solution, and enjoy this little walkthrough:
1. Open the PublishReportProject project: this sucker is the Setup Project that we use to invoke a Custom Action that does all the real work of publishing a report and fixing up its data source.
2. Right-click PublishReportProject and choose View | File System. You’ll see a new folder in this list called “TempFolder”, and it contains SampleReport.rdl. SampleReport.Rdl is a report which uses a Data Source called “MyOlap”, and MyOlap hits a cube inside a database named MySSAS. Needless to say, even once you get this thing published on your server, the report itself won’t work since you don’t have my back-end stuff…Sorry!
3. Choose View | User Interface. Under Start, you’ll see where I added 2 Textbox forms to gather information from the user. If you look at the properties of each form, you’ll see that each Label (Like “Data Source Location”) has an associated Property name(“DSL”) and a Default Value (“/Data Sources”). We’ll use the Property values later on…Also notice how I didn’t hash out the username / password…a bad idea, but I’m in a hurry.
4. Choose View | Custom Actions, and you’ll see how we launch the Custom Action which does the real work. Note the Install folder and how it contains ClassLibrary1.dll. ClassLibrary1 contains all the SSRS web service API logic that publishes our report and data source. Right-click ClassLibrary1.dll and choose Properties.
5. Eyeball the CustomActionData property. This is how we “feed” all of the information that we collected in our user interface to the class library. You’ll see the same thing over and over again here: /PropertyName = [PropertyName] , Basically, /PropertyName represents a variable in the class library (which we haven’t looked at yet), and we set this value with = [PropertyName]. [PropertyName] is the value we specified in the Edit ? Property property back in the User Interface area.
I found this part to be the trickiest thing I had to play around with…just be patient or search MSDN about how CustomActionData works.
OK…so you now should see how we:
· Copy a report (in this case SampleReport.rdl) to a temp folder on the machine that the MSI gets run on
· Collect information from the user about where to publish this report
· Push the user-provided info to a custom action which lives inside a Class Library
Next, let’s look at the Class Library itself. Open The ClassLibrary1 project, double-click Class1.cs and switch to code view.
1. First, check the web references for this project…note anything familiar? We’re just leveraging the 2005 SSRS Management endpoint…no big deal.
2. OK, check out the code next. I was lazy, so a lot of this stuff could be made more dynamic…I didn’t bother though. In the Install() method, we begin by assigning all those values we collected in the MSI UI to local variables.
3. Next, we authenticate against the web service and create a folder in SSRS called “MSI Report Demo”. We’ll be dropping our report here later on.
4. After that, it’s time to read the definition of SampleReport.rdl (which our MSI has already dumped in c:\temp). We plug the definition into an array of bytes called reportDefinition.
5. Then, we create the report with a call to CreateReport()…again, note the hardcoded values…yuk!
6. The next thing to do is re-create the data source that the report will depend on. We do so largely using the values we collected from the user earlier on. Calling CreateDataSource() pushes the data source out to the SSRS instance.
Warning: When you play with this, make sure you are regularly cleaning up (deleting) the /MSI Report Demo folder & report, AND the data source from your SSRS instance BEFORE you run the MSI..if you forget, we’ll throw an exception because I didn’t bother to handle scenarios where this stuff already exists on the server...that’s what you get for trying to use free code.
7. After the data source has been created, we have to circle back to our recently published report and “fix up” its data source reference so the report “knows” where its data source is…Note the hard coded values.
8. Finally, we make a feeble attempt to clean up by deleting the Sample Report from the file system.
I assume you will probably want to get “your” version of the publisher working as quickly as possible…Here’s what you need to do:
1. Replace the strings “SampleReport” or “SampleReport.RDL” with “YourReportName” and “YourReportName.RDL” on lines 44, 47, 88, 91, 97 of Class1.cs and anywhere else you happen to see them show up J
2. Back in the Deployment Project, delete my SampleReport.rdl as a resource.
3. Right-click the setup project and select View | System, then Add your RDL doc to the TempFolder (Right-click TempFolder, choose Add).
4. Rebuild!
5. You should be ready to install now…This worked for me every time I tried it, but of course your mileage may vary depending on what you try to publish. I suggest you start simple, then get more daring J
Also remember that when you run the MSI, you are essentially installing an application. If setup completes with no errors, don’t forget to right-click the setup project in Solution Explorer and choose Uninstall before you try and run the MSI again…
Comments
Anonymous
March 23, 2006
Good Day sir,
finaly a way to deploy reprots automatically. but before i try this can this be done in SSRS 2000 with SP2 only?
thanks
~jayjayAnonymous
March 23, 2006
You will have to modify the sample so that it uses the SSRS 2000 web service endpoint rather than the 2005 endpoint (which is what this samples utilizes)Anonymous
May 10, 2006
hi,
the setup is reading rdl files from a file system. if i want to pull all my rdl files into the temp folder in setup project and read from there itself. how do i do ?
i hope u understood the question.
instead of reading from file system , i should read the file from the project itself, that is i will embedded all my reports in the projectAnonymous
May 16, 2006
Sorry, I can't help you there...I don't know enough about setup projects myself.Anonymous
February 26, 2007
Hi !I want to deploy my reports in the sharepoint website.Could you help me how to do that with the TargetReport folder,Target server url.,Thank YouAnonymous
February 27, 2007
Preethi, see this linkhttp://blogs.msdn.com/controlpanel/blogs/posteditor.aspx?SelectedNavItem=Posts§ionid=4743&postid=1479653Anonymous
May 24, 2007
In the sample your getting parmeters from custom install of data source and reporti want to add one more parameter how can i add in the sample code.i am passing DirectoryInfo that means file folder information from custom install of data source and reportthats way i am adding this parameterstring rfl = this.Context.Parameters["RFL"];i created properties for texboxes(A) regarding my new parameter but i didn't get that parameter how can i create new parameter.below is the sample code parameters.// get parameters for custom install of data source and report string dataSourceName = this.Context.Parameters["DSN"]; string dataSourceLocation = this.Context.Parameters["DSL"]; string sqlServerName = this.Context.Parameters["DSSERVERNAME"]; string dbName = this.Context.Parameters["DBNAME"]; string uid = this.Context.Parameters["UID"]; string pwd = this.Context.Parameters["PWD"];Anonymous
May 29, 2007
can u tell me how to change the url in app.config??Anonymous
June 06, 2007
Hi, Nice Article. I have a question here. This MSI deploys reports on the machine where it is running. But how to deploy reports on remote machine? Will it sipport?ThanksAnjanAnonymous
June 08, 2007
Sure, it'll work.Anonymous
August 20, 2007
Hi,I have tried with the above solution, but how can I add more reports into MSI as I have around 220 reports in my solution.Here I can able to add only one reports at a time!Please could you help me on this issue.RaviPola.Anonymous
August 27, 2007
Ravi, you'd just write code which uses the File system object or system.io to loop through every RDL in a temp folder and push it out...Anonymous
March 12, 2008
Hi this was a nice article.But when i tried to deploy reports in a remote report server it threw the error 'Server Application Error' .The client received text/html type but expected text/xml. Please help me to solve this error.Anonymous
April 30, 2008
Hi,I have tried the below link but its asking me for user name and passwordhttp://blogs.msdn.com/controlpanel/blogs/posteditor.aspx?SelectedNavItem=Posts§ionid=4743&postid=1479653I want to deploy my reports in the sharepoint website.Could you help me how to do that with the TargetReport folder,Target server url.,Thanks,RajdiepAnonymous
May 12, 2008
This looks great. I need to do exactly what you have here. But I don't know c# at all. Do you possibly have this also in vb.net?Anonymous
June 13, 2008
HiI am not getting the design view file of textboxes in the solution.i am not getting view|user interface,view|custom action etc.please guide me....so that i can view these filesAnonymous
September 19, 2008
I need to deploy the reports to a machine that with no VS installed. Will this MSI work?Anonymous
November 18, 2008
I have developed a tool for publishing reports automatically, based on a XML configuration file; check it out here:http://blogs.ugidotnet.org/dsenatore/archive/2008/10/01/94219.aspxAnonymous
January 07, 2009
Hi!I need to set the reportserver url at runtime(using an XML file eg: http://servername/reportserver).Can we do this in your solution?Anonymous
July 26, 2010
I have deployed my reports and database to the URL, but i have to deploy databse every day else it generates blank report. pls help and provide some solution for the same,.Anonymous
October 06, 2010
Hi, for me the datasource is not createtd on the reportserver but the sample.rdl and the msi folder got created .. Please help me on this. Thanks, AnishAnonymous
October 26, 2010
How can i copy an image file to the folder in reportserverAnonymous
December 27, 2011
Given that Microsoft has deprecated VS setup and installer projects, what would be the best mechanism for creating a report installer for my application using technologies that will still be around after the next release of VS? (From msdn.microsoft.com/.../ee721500.aspx, "Future versions of Visual Studio will not include the Visual Studio Installer project templates.")