Programmatically Deploying a SQL Reporting Services 2005 Report

• Download source - 70.3 Kb

Introduction

Most of the time, it is acceptable for customers to deploy reports manually via the Visual Studio Report Manager. But sometimes, there may be a hard rule that "anything deployable, for instance, WinForms apps, WebForms apps, or SSRS reports, etc. has to be deployed via MSI.

This sample basically has a config file to collect information like where the data source for a report should be created, and what its name should be, and the user name and password that should be used in the data source. Making all this information dynamic is important because one often publishes a report and its data source points to a different location on a production server than on the machine it was originally created.

Using the code

Once the setup information is collected from the app.config, the method Install starts deploying the report on to the server.

Source Code:

Steps to be taken care of:

1. Add a Web reference to the web service exposed by the SSRS (SQL Reporting Service 2005).
2. Set the config settings [information such as report name, database connection etc.].

using System;
using System.Collections;
using System.Collections.Specialized;
using System.Configuration;
using System.Diagnostics;
using System.Reflection;
using System.IO;

using DeployReport.ReportingService2005;

namespace DeployReport
{
    public class ReportInstaller
    {
        // Variable to hold the database provider type.
        private string _extension = string.Empty;

        // Variable to hold the Servername to fetch the data for the report.
        private string _serverName = string.Empty;
        
        // Variable to hold the DataSourceLocation in the report server for
        // the deployed report.
        private string _datasourceLocation = string.Empty;
        
        // Variable to hold the parent folder name in the report server to
        // deploy the report.
        private string _parent = string.Empty;

        // Variable to hold the folder name in the report
        // server to deploy the report.
        private string _folder = string.Empty;

        // Variable to hold the ConnectionString for each report.
        private string _connectString = string.Empty;

        // Variable to hold the webservice client to access the reporting server.
        private ReportingService2005.ReportingService2005 _rs
            = new DeployReport.ReportingService2005.ReportingService2005();

        // Variable to hold the database name to fetch the data for the report.
        private string _dbName = string.Empty;

        // Variable to hold the datasource name used by the report.
        private string _datasource = string.Empty;

        // Variable to hold the full path of the report to be deployed.
        private string _reportPath = string.Empty;

        // Variable to hold the properties of finditems.
        private CatalogItem[] _returnedItems;

        // Collection to hold the reports to deploy.
        private ArrayList ConnectionStrings = new ArrayList();

        public ReportInstaller()
        {
            // This call is required by the Designer.
            InitializeComponent();

            this._extension = this.GetValueFromConfig("appSettings", "Extension");
            this._serverName = this.GetValueFromConfig("appSettings", "ServerName");
            this._datasourceLocation = this.GetValueFromConfig("appSettings",
                                       "DataSourceLocation");
            this._parent = this.GetValueFromConfig("appSettings", "Parent");
            this._folder = this.GetValueFromConfig("appSettings", "Folder");

            // Set security credentials for web service client authorization.
            _rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

            // Create report folder to drop report in.
            if ( this._folder.Length > 0 && this._parent.Length > 0)
            {            
                // Create folder if not exists.
                if (this.CheckExist(ItemTypeEnum.Folder,
                    this._parent, this._folder) == false)
                {
                    _rs.CreateFolder( this._folder, this._parent, null);
                }
            }
            else
            {
                // Log the error.
            }

            this.GetReportsToDeploy( "ReportDetails");
            this.Deploy();
        }

        
        // Deploys the report to the reporting server.
        private void Deploy()
        {            
            foreach (object _report in this.ConnectionStrings)
            {
                // Get the connection string for the report to deploy.
                //this._connectString = 
                //      this.GetValueFromConfig( "ReportDetails",
                //    "Report1");
                this._connectString = _report.ToString();

                // Continue deploying report if report details provided.
                if ( this._connectString.Length > 0)
                {
                    // Set report details.
                    this.SetReportInfo();

                    // Open the report (rdl) file and
                    // read the data into the stream.
                    byte[] _reportDefinition;
                    Warning[] _warnings;
                    FileStream _stream = File.OpenRead( this._reportPath);
                    _reportDefinition = new Byte[_stream.Length];
                    _stream.Read( _reportDefinition, 0, (int)_stream.Length);
                    _stream.Close();

                    // Create the report into the server.
                    string _reportName = this._reportPath.Substring(
                        this._reportPath.LastIndexOf("\\") + 1);
                    _warnings = (Warning[])_rs.CreateReport( _reportName.Remove
                        ( _reportName.Length - 4, 4), 
                        this._parent + this._folder, true,
                        _reportDefinition, null);

                    // Create datasource for the report.
                    DataSource dSource = new DataSource();
                    DataSourceDefinition dDefinition = 
                            new DataSourceDefinition();
                    dSource.Item = dDefinition;
                    dDefinition.Extension = this._extension;
                    dDefinition.ConnectString = @"Data Source=" + this._serverName
                        + @";Initial Catalog=" + this._dbName;
                    dDefinition.ImpersonateUserSpecified = true;
                    dDefinition.Prompt = null;
                    dDefinition.WindowsCredentials = true;
                    dDefinition.CredentialRetrieval = 
                           CredentialRetrievalEnum.Integrated;
                    dSource.Name = this._datasource;
                    
                    try
                    {
                        if (this.CheckExist( ItemTypeEnum.DataSource, this._parent,
                            this._datasourceLocation + "/" + 
                            this._datasource) == false)
                        {
                            _rs.CreateDataSource( this._datasource, @"/" +
                                this._datasourceLocation, false, dDefinition, null);
                        }
                    }
                    catch (System.Web.Services.Protocols.SoapException _exception)
                    {
                        throw _exception;
                    }

                    // Report and Datasource created,
                    // now fix up datasource reference to
                    // make sure report points at correct dataset.
                    try
                    {
                        DataSourceReference reference = new DataSourceReference();
                        DataSource ds = new DataSource();
                        reference.Reference = @"/" + this._datasourceLocation + @"/"
                            + this._datasource;
                        DataSource[] dsarray = _rs.GetItemDataSources( this._parent +
                            this._folder + "/" + _reportName.Remove
                            ( _reportName.Length - 4, 4));
                        ds = dsarray[0];
                        ds.Item = (DataSourceReference)reference;
                        _rs.SetItemDataSources( this._parent +
                            this._folder + "/" + _reportName.Remove
                            ( _reportName.Length - 4, 4), dsarray);
                    }
                    catch (Exception _exception)
                    {
                        throw (_exception);
                    }
                }
            }
        }

        /// This function reads a setting from the config file.Empty string
        /// returned if no setting was found. Raise error if config can not be
        /// read 
        /// <param name="sectionName">This is the section name in the config
        /// file</param>
        /// <param name="keyName">This is the name of the key in the specfied
        /// section in the config file</param>
        /// <returns>string</returns>
        private string GetValueFromConfig(string sectionName, string keyName)
        {
            string _value = string.Empty;
            try
            {
                // Read the configuration settings from the configuration file
                NameValueCollection _section = (NameValueCollection)
                    ConfigurationSettings.GetConfig( sectionName);

                // If there is a key return , else return empty string
                if (_section != null && _section[keyName] != null)
                {
                    _value = _section[keyName];
                }
            }
            catch (Exception _exception)
            {
                // Consume the error, so that the service keeps running,
                // but log the error.
                EventLog.WriteEntry( Assembly.GetExecutingAssembly().FullName,
                    _exception.Message, EventLogEntryType.Error);
            }
            return _value;
        }

        /// Checks if the folder exists or not.
        /// <param name="path">Parent folder path</param>
        /// <param name="folderName">Name of the folder to search</param>
        /// <returns>True if found, else false.</returns>
        private bool CheckExist(ItemTypeEnum type, string path, string folderName)
        {
            string _path = path + folderName;

            // Condition criteria.
            SearchCondition[] conditions;

            // Condition criteria.
            SearchCondition condition = new SearchCondition();
            condition.Condition = ConditionEnum.Contains;
            condition.ConditionSpecified = true;
            condition.Name = "Name";
            condition.Value = "";
            conditions = new SearchCondition[1];
            conditions[0] = condition;

            this._returnedItems = _rs.FindItems( path, 
                                  BooleanOperatorEnum.Or, conditions);

            // Iterate thro' each report properties to get the path.
            foreach (CatalogItem item in _returnedItems)
            {
                if (item.Type == type)
                {
                    if (item.Path == _path)
                        return true;
                }
            }
            return false;
        }

        /// Sets report definition such
        /// as databasename to connect, datasource
        /// of the report and path for the report.
        private void SetReportInfo()
        {
            string[] _temp = this._connectString.Split( ";".ToCharArray());
            string[] _properties = new string[_temp.Length];

            for (int _count = 0; _count < _temp.Length; _count++)
            {
                _properties[_count] = 
                   _temp[_count].Split("=".ToCharArray())[1];
            }

            // Get the deployment info for the report.
            if (_properties.Length == 3)
            {
                this._dbName = _properties[0];
                this._datasource = _properties[1];
                this._reportPath = _properties[2];
            }
        }

        /// Gets all the tags under the section.
        private void GetReportsToDeploy(string sectionName)
        {
            try
            {
                // Read the configuration settings from the configuration file
                NameValueCollection _section = (NameValueCollection)
                    ConfigurationSettings.GetConfig( sectionName);
                this.ConnectionStrings.Clear();

                if (_section != null && _section.HasKeys() == true)
                {
                    for (int _count = 0; _count < _section.Keys.Count; _count++)
                    {
                        this.ConnectionStrings.Add( _section[_section.Keys[_count]]);
                    }
                }
            }
            catch (Exception _exception)
            {
                throw _exception;
            }
        }
    }
}

That's all!