Click here to Skip to main content
11,705,617 members (52,844 online)
Click here to Skip to main content

Programmatically Deploying a SQL Reporting Services 2005 Report

, 9 Jun 2006 153.2K 1.5K 74
Rate this:
Please Sign up or sign in to vote.
This article explains how to deploy SQL reports programmatically into Reporting Server 2005, using Web Services exposed by SQL Reporting Services 2005.

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!

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Sivakumar Mahalingam
Web Developer
France France
No Biography provided

You may also be interested in...

Comments and Discussions

 
QuestionCan you tell me how to Create a Custom Datasource to the report? Pin
Srinivas3923-Sep-11 4:14
memberSrinivas3923-Sep-11 4:14 
GeneralSecureConnectionLevel=0 in rsreportserver.config Pin
mahi988516-Feb-10 9:06
membermahi988516-Feb-10 9:06 
QuestionWhat is the key="Parent" value should be Pin
ruki00916-Nov-09 1:31
memberruki00916-Nov-09 1:31 
QuestionDeploying to different Reporting Services servers Pin
VMeitlis28-Apr-09 9:04
memberVMeitlis28-Apr-09 9:04 
GeneralUse RSBuild as an Alternative Pin
visualjoel7-Jan-09 0:45
membervisualjoel7-Jan-09 0:45 
GeneralGenius, pure genius! Pin
Member 186867610-Nov-08 10:00
memberMember 186867610-Nov-08 10:00 
QuestionHow toCreate Virtual Directories in Reporting Services Pin
Karuna Kumar8-Oct-07 20:49
memberKaruna Kumar8-Oct-07 20:49 
QuestionCreating Virtual Directories in Reporting Services Pin
Karuna Kumar8-Oct-07 20:46
memberKaruna Kumar8-Oct-07 20:46 
Generaldeploy report Pin
phyomgmgwan26-Sep-07 17:28
memberphyomgmgwan26-Sep-07 17:28 
Generalreport server name Pin
SMSM9225-Sep-07 4:12
memberSMSM9225-Sep-07 4:12 
NewsRS build Pin
Syed Mehroz Alam6-Aug-07 19:57
memberSyed Mehroz Alam6-Aug-07 19:57 
QuestionUse windows xp with SSRS Pin
farah78m18-Jul-07 21:21
memberfarah78m18-Jul-07 21:21 
Questionerror:createDataSource [modified] Pin
Anju-H25-Apr-07 5:49
memberAnju-H25-Apr-07 5:49 
AnswerRe: error:createDataSource Pin
jcarlos1013-Sep-11 11:34
memberjcarlos1013-Sep-11 11:34 
GeneralRe: error:createDataSource Pin
jcarlos1013-Sep-11 12:26
memberjcarlos1013-Sep-11 12:26 
Questionhow to delete report ? Pin
Anju-H23-Apr-07 5:00
memberAnju-H23-Apr-07 5:00 
AnswerRe: how to delete report ? Pin
CTGuy6719-Oct-07 7:33
memberCTGuy6719-Oct-07 7:33 
GeneralI think you ref's part is incomplete Pin
dr-Wicked4-Apr-07 9:35
memberdr-Wicked4-Apr-07 9:35 
Questionsharepoint integration?? Pin
Stefanos V.28-Feb-07 23:41
memberStefanos V.28-Feb-07 23:41 
QuestionIs there a way to Store Credentials with the report? Pin
CraigRCervo13-Feb-07 10:46
memberCraigRCervo13-Feb-07 10:46 
AnswerRe: Is there a way to Store Credentials with the report? Pin
CraigRCervo15-Feb-07 2:40
memberCraigRCervo15-Feb-07 2:40 
Questionquery on SetItemDataSources Pin
ikutoshi28-Nov-06 0:32
memberikutoshi28-Nov-06 0:32 
GeneralConfig File Pin
nijntjeRoelt23-Nov-06 23:37
membernijntjeRoelt23-Nov-06 23:37 
GeneralRe: Config File Pin
Sivakumar Mahalingam4-Jan-07 19:28
memberSivakumar Mahalingam4-Jan-07 19:28 
AnswerRe: Config File Pin
Sivakumar Mahalingam4-Jan-07 19:29
memberSivakumar Mahalingam4-Jan-07 19:29 
GeneralRe: Config File Pin
Padoor Shiras19-Jul-07 20:28
memberPadoor Shiras19-Jul-07 20:28 
GeneralRe: Config File Pin
Sivakumar Mahalingam20-Jul-07 4:52
memberSivakumar Mahalingam20-Jul-07 4:52 
QuestionRe: Config File Pin
Karuna Kumar8-Oct-07 20:39
memberKaruna Kumar8-Oct-07 20:39 
QuestionRe: Config File Pin
Karuna Kumar7-Oct-07 22:07
memberKaruna Kumar7-Oct-07 22:07 
QuestionCatalogItem, ItemTypeEnum?? Pin
Vikram Seri31-Oct-06 2:32
memberVikram Seri31-Oct-06 2:32 
AnswerRe: CatalogItem, ItemTypeEnum?? Pin
Rev Kev24-Jan-07 4:53
memberRev Kev24-Jan-07 4:53 
QuestionConfig File? Pin
champ197920-Oct-06 7:51
memberchamp197920-Oct-06 7:51 
GeneralMore info on app.config file Pin
static readonly11-Oct-06 8:37
memberstatic readonly11-Oct-06 8:37 
GeneralRe: Web application Pin
Sp_Prabu24-Apr-07 11:15
memberSp_Prabu24-Apr-07 11:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150819.1 | Last Updated 9 Jun 2006
Article Copyright 2006 by Sivakumar Mahalingam
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid