A Simple GUI Tool for SQL 2005 Reports deployment without using BI Development Studio

• Download SQL2005ReportDeployer_Demo - 65.03 KB
• Download SQL2005ReportDeployer_Src.zip - 516.33 KB

Introduction

SQL 2005 Reports deployment is done either by choosing the deploy option in the BI Development studio, then specifying the target Server URL and the target Report Folder, which is the standard way, or deploying the reports manually by opening the Report Server URL, and creating the DataSource, the parent Folder where you will add the datasource and report in, and the attaching the Reports manually, which is still not that realistic as you don’t have to do all that each time you want to deploy a report. Yet the first option is not always possible, as you may wish to deploy a report while you don’t have the BI development Studio installed in your machine. In this article, I expect that you know both techniques before reading, as I won’t be explaining in details everything.

I made a simple GUI tool that helps in deploying the SQL Reports by consuming the web service named reportservice.asmx that the SQL Reporting Server produces and hosted by the IIS of the Report server. Usually the Report Server has the URL as:
http://<server name>/ReportServer
and sometimes as http://<server name>/ReportServer$SQLExpress, or http://<server name>/ReportServer$SQL2005. This is according to what you named the SQL server Instance installed. Anyway, let’s get back to the main point. The webservice within the ReportsServer web application hosted in the IIS of the server machine actually does everything you need to deploy the Reports you want; I guess it is better to have a look at the ReportingService2005 Methods.

Anyway... Let’s start having a look at the application and the code. First I will be investigating the application, and what does everything means, then we will have a quick review on the main parts of the code.

How to use the Tool?

Once you run the application, a window will appear asking you to enter the Report Manager URL, as to connect to its web service. I.e. it’s the same URL you type in the target server URL property when using the BI Development Studio for deployment. The URL you entered is saved then so that you don’t have to type it in each and every time you run the application. I use this URL, so as to modify the WebService I am consuming, to connect to the URL you will choose. To know what I mean, just have a look at this article by Christopher G. Lasater, that describes what I did actually (Dynamic Web Service).

After the previous step is done, the Web Service now is made ready to do the rest.

Next, to get started, you have to define the following:

1. The connection string as the data source.
2. The Data source name.
3. The Report source (Physical Path in your machine).
4. The Report Name (The name that you will give to the Report you just browsed for).
5. Finally, the folder (a logical folder in the Report Server not a physical folder in your machine) which you will save in it the Reports and the data sources you want.

Finally, after running the Tool successfully, then opened the Report Manager, you will find a new Folder as you named in the tool, and has the DataSources and Reports you made as below.

Code Overview

If you had a look at the ReportingService2005 Methods, you will find the two main functions that you will be using mainly. They are the CreateDataSource function, and the CreateReport function.

A brief description of how to use the article or code. The class names, the methods and properties, any tricks or tips.

This is the part which Creates the DataSource

public void CreateReportDataSource(string name, string extension, string connectionString)
        {
            listView1.Items.Add("Creating Data Source [" + txt_DataSource.Text + "]");
            ReportService.DataSourceDefinition definition = new ReportServerInstaller.ReportService.DataSourceDefinition();
            definition.CredentialRetrieval = ReportService.CredentialRetrievalEnum.Integrated;
            definition.ConnectString = connectionString;
            definition.Enabled = true;
            definition.Extension = extension;

            try
            {
                rs.CreateDataSource(name, FolderName, chk_DataSourceOverwrite.Checked, definition, null);
                listView1.Items.Add("Data source: [" + name + "] created successfully.");
                TS_Progress.PerformStep();
            }
            catch (Exception ex)
            {
                listView1.Items.Add("ERROR creating data source: " + name);
                throw ex;
            }
        }

And here is the part which Publishes the report

private void PublishReport(string reportName)
        {
            listView1.Items.Add("Publishing Report [" + reportName + "]");
            FileStream stream = File.OpenRead(ReportSource);
            definition = new byte[stream.Length];
            stream.Read(definition, 0, int.Parse(stream.Length.ToString()));
            stream.Close();

            try
            {
                rs.CreateReport(txt_ReportName.Text, FolderName, chk_ReportOverwrite.Checked, definition, null);
                listView1.Items.Add("Report: [" + reportName + "] created successfully.");
                TS_Progress.PerformStep();
            }
            catch (Exception ex)
            {
                listView1.Items.Add("ERROR creating Report: " + reportName);
                throw ex;
            }

        }

I have attached an installer for easy use of the tool. You don’t have to configure anything before use. Just insure that you have the correct SQL Manager URL typed in the first form, as this does everything. Happy Deployment! ;)