Automating Report Deployment with Reporting Services in SharePoint Integration Mode


Automating Report Deployment in Reporting Services can be challenging.  SharePoint offers many modes of deployment of files.  Unfortunately the out of the box deployment for SharePoint does not allow for automatic hook up of data sources.  So you can deploy the rdl's using SPFile upload functions on the SPWeb of your choice but those rdls will exist without data sources.  Here are the steps you will need to follow to deploy the reports automatically


1. Create the document library


2. Activate the Report Server Feature - no example but you'll need to check using something like SPContext.Current.Site.Features["367B94A9-4A15-42BA-B4A2-32420363E018"] != null


3.  Add the reporting services content type to the document libraries


4. Add the data sources


5. Add the reports


6. Associate the data sources with the reports


Here is my report manager that is an excellent example of being able to add reports dynamically from the file system.  In order to upload the report you'll need to call the ReportingServices2006 web service.  The reports manager is accessed by the Feature Activator on a custom feature just to give you an idea of how you might drive this.  The entry method is Deploy.


class RSHelper
    {
        public const string DOCLIBRARYNAME = "SomeLibrary";


        string dataSourceName = "somedatasource" ;
        ReportingService2006 rs;
        string libraryUrl;
        string path;
        SPWeb web;


  public void Deploy(string path, SPWeb web)
        {
            this.path = path;
            this.web = web;
            this.libraryUrl = string.Format("{0}/{1}/", web.Url, DOCLIBRARYNAME);
            rs = new ReportingService2006();
            rs.Url = web.Site.Url + "/_vti_bin/ReportServer/ReportService2006.asmx";


            rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
            CreateReportsLibrary();
            AddDataSourceToLibrary();
            AddReportToLibrary();
        }


       private  void CreateReportsLibrary()
        {
            Guid guid;


            SPDocumentLibrary list = null;
            //Not good practice but dictionary throws an exception when item not there.
            foreach (object o in web.Lists)
            {
                SPDocumentLibrary tempLib = o as SPDocumentLibrary;
                if (tempLib != null && tempLib.Title == DOCLIBRARYNAME)
                {
                    list = tempLib;
                    break;
                }
            }


            if (list == null)
            {
                guid = web.Lists.Add(DOCLIBRARYNAME, "", SPListTemplateType.DocumentLibrary);
                list = web.Lists[guid] as SPDocumentLibrary;
            }


//Add RS Content Types 
            (list).ContentTypesEnabled = true;
            SPContentTypeId id;
            SPContentType contentType;
            id = new SPContentTypeId("0x010100C3676CDFA2F24E1D949A8BF2B06F6B8B");//rdl
            contentType = web.AvailableContentTypes[id];
            if (list.ContentTypes[contentType.Name] == null)
            {
                (list).ContentTypes.Add(contentType);
            }
            id = new SPContentTypeId("0x0101007DFDDF56F8A9492DAA9366B83A95B3A0");//rsds
            contentType = web.AvailableContentTypes[id];
            if (list.ContentTypes[contentType.Name] == null)
            {
                list.ContentTypes.Add(contentType);
            }
            id = new SPContentTypeId("0x010100D8704AF8ED734F4088724751E0F2727D");//model
            contentType = web.AvailableContentTypes[id];
            if (list.ContentTypes[contentType.Name] == null)
            {
                (list).ContentTypes.Add(contentType);
            }
        }


        private void AddDataSourceToLibrary()
        {
            DataSourceDefinition dsd = new DataSourceDefinition();
            dsd.CredentialRetrieval = CredentialRetrievalEnum.None;
            dsd.ConnectString = "data source=.;initial catalog=someDB;Trusted_Connection=true";
            dsd.Enabled = true;
            dsd.EnabledSpecified = true;
            dsd.Extension = "SQL";
            dsd.ImpersonateUserSpecified = false;
            //Use the default prompt string.
            dsd.Prompt = null;
            dsd.WindowsCredentials = false;
            rs.CreateDataSource(dataSourceName+".rsds", libraryUrl, true, dsd, null);


        }


        private void AddReportToLibrary()
        {
            Byte[] definition = null;
            Warning[] warnings = null;
            string[] files = Directory.GetFiles(path);
            foreach (string file in files)
            {
                FileStream stream = File.OpenRead(file);
                definition = new Byte[stream.Length];
                stream.Read(definition, 0, (int)stream.Length);
                stream.Close();
                string filename = file.Replace(path, "");
                string parent = libraryUrl;
                if (file.Contains("rdl"))
                {
                    CatalogItem report = rs.CreateReport(filename, parent,
                                true, definition, null, out warnings);
                    AddDataSource(report);
                }
                else
                {
                    string mime = "image/gif";
                    CatalogItem report = rs.CreateResource(filename, parent,
                             true, definition, mime, null);
                }
                if (warnings != null)
                {
                    foreach (Warning warning in warnings)
                    {
                        Logger.Log(warning.Message);
                    }
                }
                else
                    Logger.Log(string.Format("Report: {0} created successfully " +
                                      " with no warnings", file));
            }


        }


        private void AddDataSource(CatalogItem report)
        {
            DataSourceReference reference = new DataSourceReference();
            reference.Reference = string.Format("{0}{1}.rsds",libraryUrl,dataSourceName);
            DataSource ds = new DataSource();
            ds.Item = reference;
            ds.Name = dataSourceName;
            rs.SetItemDataSources(report.Path, new DataSource[] { ds });
        }
    }

Comments (11)
  1. Anonymous2572918446 says:

    I was trying to run this code and I am getting an exception on the

    rs.SetItemDataSources(report.Path, new DataSource[] { ds });

    The reports and the data source get deployed to my Sharepoint report library, but the program soon breaks at the line above.

    The exception is:

    System.Web.Services.Protocols.SoapException was unhandled

     Message="System.Web.Services.Protocols.SoapException: The data source ‘DataSource1’ cannot be found. —>

    Microsoft.ReportingServices.Diagnostics.Utilities.DataSourceNotFoundException: The data source ‘DataSource1’ cannot be found.n  

    at Microsoft.ReportingServices.DataExtensions.DataSourceInfoCollection.CombineOnSetDataSources(DataSourceInfoCollection newDataSources)n  

    at Microsoft.ReportingServices.Library.SetItemDataSourcesAction.SetReportDataSources(CatalogItem item, DataSource[] dataSources)n  

    at Microsoft.ReportingServices.Library.SetItemDataSourcesAction.PerformActionNow()n   at Microsoft.ReportingServices.Library.RSSoapAction`1.Execute()n  

    at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.SetItemDataSources(String Item, DataSource[] DataSources)n  

    — End of inner exception stack trace —n  

    Am not pasting the entire trace since it’s large.

    Do you know what’s wrong here?

  2. shawnfel says:

    Its probably because your report is pointing to a datasource that doesn’t exist yet.  You need to make sure that DataSource1 is already created before you setitemdatasources

  3. owain says:

    Shawn

    Would it be possible for you to post a solution/ .cs file  featuring this code on this site. I am unsure of the using statements required and any references needed

    regards

    owain

  4. owain says:

    Shawn,

    further to my earlier comment. These are my error messages.

    The type or namespace name ‘ReportingService2006’ could not be found (are you missing a using directive or an assembly reference?)

    The type or namespace name ‘SPWeb’ could not be found (are you missing a using directive or an assembly reference?)

    The type or namespace name ‘CatalogItem’ could not be found (are you missing a using directive or an assembly reference?)

    owain

  5. I had made a previous post with a small code sample on how to upload report deployment. Now I’m improving

  6. shawnfel says:

    I added a new post with sample code, please check that out, its automated based on an xml file.  Let me know if you have any issues. please comment with your questions.

  7. FBA Question says:

    I was unable to upload reports when using Forms based authentication.  How would you pass the FBA credentials in using the code?

    Any help is appreciated.

    rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

  8. shawnfel says:

    That sounds like a tough one.  I would say use a second alternate access mapping but sharepoint ssrs integration doesn’t support two aam’s.  Have you looked at this article http://msdn.microsoft.com/en-us/library/bb975135.aspx

  9. FBQ Question says:

    Using the Authentication web service did the trick!  Thanks for the help!

  10. Porkinz says:

    I encountered the same DataSourceNotFoundException. The solution for me was by using the ReportServer.GetItemDataSources method on the report you are trying to hook up to the datasource file. It returns all the datasources referenced by the report. Go through the DataSources with invalid references (InvalidDataSourceReference) and fix them by setting the correct reference to the datasource file in each datasource.

    Here is the example

    private void AddDataSource(CatalogItem report, string dataSourceFileName)

           {

               DataSource[] referencedDataSources = rsClient.GetItemDataSources(report.Path);

               foreach (DataSource dataSource in referencedDataSources)

               {

                   //Fix Invalid references to the DataSource of the Report.

                   if (dataSource.Item.GetType().ToString() == typeof(InvalidDataSourceReference).ToString())

                   {

                       DataSourceReference reference = new DataSourceReference();

                       reference.Reference = string.Format("{0}/{1}", docLibraryUrl, dataSourceFileName);

                       dataSource.Item = reference;

                   }

               }

               rsClient.SetItemDataSources(report.Path, referencedDataSources);

           }

  11. Dr.Sharepoint says:

    You can also use BIDS To deploy to a dev server, creating the rads files in the process. Then you can pull the rsds files out anyplace them In a visual studio project With the rdl file and deploy it to a doc library as a module. This is ow I deploy across different dev farms and prod.

Comments are closed.

Skip to main content