Push SSRS Report to Sharepoint Document Library with SSIS

In case you use Microsoft Reporting Services in Native Mode and want to deliver your subscriptions to a Sharepoint Document Library you may run into some troubles with standard subscriptions.

With Reporting Services Subscriptions you can send an email or deliver the report to a shared folder.

Microsoft SQL Server Integration Services (SSIS)

The bridge between the report server and Sharepoint is SSIS, Microsofts corporate “IFTTT” 😉

Service user

First of all you need a service user with permissions to execute the report and add a document to the library.

Create a new SSIS Package

( you may run your visual studio with the service account )

Create a new package and add a script component and set the Script Language to “Microsoft Visual C# 2012”

add the following C# Code

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net;
using System.IO;

namespace ST_99ff82c376eb40a2829b74f045a2f3be
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        public void Main()
            WebClient myWebClient;
            string RemoteURI;
            string LocalFileName;
            string DestinationURL;
            bool FireAgain = true;

            Dts.Log("Downloading Report", 999, null);

                myWebClient = new WebClient();
                // URL Parameters to generate a PDF file
                RemoteURI = "https://<REPORTSERVER>/ReportServer/Pages/ReportViewer.aspx?/<PATH>/<REPORT NAME>&rs:Command=Render&rs:Format=PDF";

                // create a temporaray file in AppData Folder
                LocalFileName = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "/" + Guid.NewGuid().ToString("N");

                // Destination file name with year and month appended ( report2014-03.pdf )
                DestinationURL = "https://<SHAREPOINT URL>/report" + DateTime.Now.ToString("yyyy-MM") + ".pdf";

                // Notification 
                Dts.Events.FireInformation(0, String.Empty, String.Format("Downloading '{0}' from '{1}'", LocalFileName, RemoteURI), String.Empty, 0, ref FireAgain);

                //myWebClient.Credentials = new NetworkCredential("user", "password","domain");
                myWebClient.Credentials = CredentialCache.DefaultNetworkCredentials;
                // Download the file 
                myWebClient.DownloadFile(RemoteURI, LocalFileName);

                Dts.Events.FireInformation(0, String.Empty, String.Format("Uploading '{0}' to '{1}'", LocalFileName, DestinationURL), String.Empty, 0, ref FireAgain);
                // upload the file
                myWebClient.UploadFile(DestinationURL, "PUT", LocalFileName);

                //delete tmp file

                Dts.TaskResult = (int)ScriptResults.Success;
            catch (Exception ex)
                // Catch and handle error 
                Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;

5 Responses to “Push SSRS Report to Sharepoint Document Library with SSIS”

  1. Mark

    Having a hard time getting this to work in VS2008. Trying to adapt this to upload an Excel file to a Sharepoint document library.

    • Hannes

      Seems easy to me. Just skip the download of the report and upload the local excel file, but I know it’s never that easy 😉

  2. Sundaraguru S

    It is very useful post. I was able to PUSH the reports successfully from Visual Studio. However when I tried to run the SSIS package from Management Studio / SSIS Catalog. I am getting the “An Exception occurred during the webclient request”.

    any help or suggestion on this

    I really appreciate any help on this.
    Sundaraguru S

    • hannes

      Hi, Normally I’d check permissions (because MGMT Studio may run under a different user account), and directories (the execution directory may be different).
      The script component has barely any error handling, so it’s nearly impossible to find the problem there.
      One of my colleagues suggested to create a new C# gui application add the code to the onClick event of a new button and execute.
      The error handling is much better and maybe it points you the right direction.
      br, Hannes

  3. Sundaraguru S


    Thanks for your quick response. I have fixed the issued.

    I had a Write permission issue for the “Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)”. So I have changed this one into Share Path.

    Sundaraguru S


Leave a Reply