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: 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.
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.
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
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.
