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
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    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);

            try
            {
                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";
                Console.WriteLine(RemoteURI);
                Console.WriteLine(LocalFileName);

                // 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
                File.Delete(LocalFileName);

                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;
            } 
        }
    }
}