Execute a SQL Server Reporting Services report from Integration Services Package

You have a requirement where a user does not want to use the SQL Server Reporting Services (SSRS) report subscription service, but wants to execute the SSRS report from a SQL Server Integration Services Package. In this case, whenever the user executes the package, a particular SSRS report will be executed and exported into Excel.  The exported Excel file will be saved in a shared folder. In this tip I will demonstrate how to solve this problem.

Problem

You have a requirement where a user does not want to use the SQL Server Reporting Services (SSRS) report subscription service, but wants to execute the SSRS report from a SQL Server Integration Services Package. In this case, whenever the user executes the package, a particular SSRS report will be executed and exported into Excel.  The exported Excel file will be saved in a shared folder. In this tip I will demonstrate how to solve this problem.

Solution

This tip assumes that you have previous real world work experience building a simple SSRS Report and SSIS package. I will use AdventureworksDW2008R2 sample database and SQL Server 2012 to demonstrate the problem and solution.

I have divided this tip in two parts.

Part 1: I will create a sample SSRS report and deploy it to the Report Server.
Part 2: I will create a SSIS Package which will execute the SSRS report created in Part1.

Part 1: Create SSRS Report and deploy it to Report Server

Step 1: Add Report item in SSRS

I have added a report item in my report project. My report name is SSIS_Execute_SSRS_REPORT. Refer to the image below.

If you are new to SQL Server Reporting Services, check out this tutorial and these tips.

Step 2: Add Data Sources in Reporting Services

I have already created an embedded data source connection to AdventureworksDW2008R2 database. Refer to the image below.

Step 3: Add a Dataset in SSRS

I am creating a new Dataset, this dataset returns two data fields (Productkey and EnglishProductName) and it has one@Productkey Query Parameter. Refer to the image below.

Dataset Query

Select Productkey, EnglishProductName
From DimProduct
Where Productkey= @Productkey

As you can see from the image below, the Dataset has been created with one Report parameter - @Productkey.

Step 4: Add Tablix in SSRS

For data viewing purposes, I am adding a Tablix into my report. This Tablix will show the Productkey and EnglishProductName. Refer to the image below.

Step 5: Report Deployment

Please follow the steps below to deploy the report on Report Server.

• Right click on Report Project which contains your report and then click on Properties. Refer to the image below.

Once you clicked on Properties; it will open a new Property Pages window. Here you have to enter theTargetReportFolder name and TargetServerURL. TargetServerURL is the URL for the Report Server andTargetReportFolder is a folder on the Report Server where the report will be deployed. If the TargetReportFolderfolder is not present on the Report Server then it will be created in the deployment process. As you can see from the image below I have already filled the required information. TargetReportFolder and TargetServerURL may differ in your case, make the changes accordingly and click OK.

• Right click on the report which you want to deploy on Report Server and click on deploy. It will deploy the report on the Report Server. Refer to the image below.

I am deploying my report; on successful deployment you will get a similar message as shown below.

The above message tells that SSIS_Execute_SSRS_REPORT report was deployed to "http://localhost:8080/ReportServer" Report Server under MyReports folder.

Part2: Create SSIS Package to Execute an SSRS Report

In this part of the tip, I will be demonstrate how to create an SSIS Package to execute an SSRS report. Please follow all the steps listed below.

Step 1: Create an SSIS Package

I have already created a new package name as SSRS_Report_Execute. 

If you are new to SQL Server Integration Services, check out this tutorial and these tips.

Step 2: Creates Variables in SSIS

Create two variables with package scope.

• Folder_Destination - Data Type for this variable is String. Please assign the variable value asC:\SSRS_Report_Execute. This variable holds the folder path where the exported file will be saved. You have to make sure that this folder is present at the defined location, otherwise the SSIS Package will fail.
• ReportParameter - Data Type for this variable is String. Please assign the variable value as 1. This variable holds the parameter value which needs to be passed into the SSRS report.

I have assigned values for both the variables; refer to the image below.

Step 3: Create a Windows Folder

Create a folder named SSRS_Report_Execute on the root of the C drive. This folder name and location depends onFolder_Destination variable value. I have assigned the C:\SSRS_Report_Execute value to a Folder_Destinationvariable in the previous step.

Step 4: Drag the SSIS Script Task

Drag the Script Task component from the toolbox into the control flow and then right click on the script task and click on edit.  Refer to the image below.

Once you click on edit button it will open the Script task editor window. Choose Microsoft Visual Basics 2010 as the Script language and select Folder_Destination and ReportParameter variables as Read only variables. Once the above two selections are done then click on Edit Script.  Refer to the image below.

Once you click on Edit Script task, it will open Script Task editor window. Please replace all auto generated VB code with the below VB Code below and save it.

Script Task VB Code

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.ComponentModel
Imports System.Diagnostics
<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
        Dim loRequest As System.Net.HttpWebRequest
        Dim loResponse As System.Net.HttpWebResponse
        Dim loResponseStream As System.IO.Stream
        Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
        Dim laBytes(256) As Byte
        Dim liCount As Integer = 1
        Try
            loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
            loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials
            loRequest.Timeout = 600000
            loRequest.Method = "GET"
            loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
            loResponseStream = loResponse.GetResponseStream
            Do While liCount > 0
                liCount = loResponseStream.Read(laBytes, 0, 256)
                loFileStream.Write(laBytes, 0, liCount)
            Loop
            loFileStream.Flush()
            loFileStream.Close()
        Catch ex As Exception
        End Try
    End Sub
    Public Sub Main()
        Dim url, destination As String
        destination = Dts.Variables("Folder_Destination").Value.ToString + "\" + "Report_" + Dts.Variables("ReportParameter").Value.ToString + "_" + Format(Now, "yyyyMMdd") + ".xls"
        url = "http://localhost:8080/ReportServer?/MyReports/SSIS_Execute_SSRS_Report&rs:Command=Render&Productkey=" + Dts.Variables("ReportParameter").Value.ToString + "&rs:Format=EXCEL"
        SaveFile(url, destination)
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

Your script task VB code must look like as below image.

Based on the requirement the user has to modify the URL and Destination variables in the Public Sub Main() function (highlighted in the code with the rectangle box). The URL variable contains the path of the report for the report server and the Destination variable contains the folder path where the file needs to be saved with a dynamic file name.

The URL is a combination of ReportServerurl + TargetReportFolder + ReportName + ReportParameter + ReportRenderingformat.

In my case:

ReportServerurl is http://localhost:8080/ReportServer
TargetReportFolder is MyReports
ReportName is SSIS_Execute_SSRS_Report
ReportParameter is Productkey
ReportRenderingformat is rs:Format=EXCEL

So the URL is "http://localhost:8080/ReportServer?/MyReports/SSIS_Execute_SSRS_Report&rs:Command=Render&Productkey=" + Dts.Variables("ReportParameter").Value.ToString + "&rs:Format=EXCEL"

Step 5: Execute Script Task

Please assign the ReportParameter variable value.  The value assigned in the ReportParameter variable value will be passed into the SSRS report as report parameter value. Let's execute the script task; on a successful execution it will export the file to specified folder location.