Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Execute a SQL Server Reporting Services report from Integration Services Package


By:   |   Read Comments (34)   |   Related Tips: > Reporting Services Custom Report

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.

Adding new SSRS Report

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.

Adding new Data Source

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.

Adding new DataSet

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.

Report Data Pane after adding Data Source and Dataset

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.

Adding Tablix in Report body

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.

Report Project Property

Once you clicked on Properties; it will open a new Property Pages window. Here you have to enter the TargetReportFolder name and TargetServerURL. TargetServerURL is the URL for the Report Server and TargetReportFolder is a folder on the Report Server where the report will be deployed. If the TargetReportFolder folder 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.


Report Project Page Property Window

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

Report Deployment

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

Report Deployment Message

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 as C:\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.

Creating Variables at package scope

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 on Folder_Destination variable value. I have assigned the C:\SSRS_Report_Execute value to a Folder_Destination variable 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.

Adding Script task in control flow

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.

Script Task Editor Window

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.

Script Task Script Editor Window

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.

Script Task Execution Window
Next Steps


Last Update:






About the author
MSSQLTips author Ghanesh Prasad Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Tuesday, March 28, 2017 - 4:51:34 AM - Shane Back To Top

 

 

Hi Ganesh

I used your process below and was having an issue whereby the report generated but the file was blank and threw an error when trying to open it.

For anyone else experiencing this problem, I found that the Parameter Name in the script task is case sensitive and must exactly match the parameter name in the SSRS report

Once I set the parameter name in the script task to exactly match the parameter in the SSRS report, the process worked fine and the files were generated as expected.

 


Friday, March 17, 2017 - 1:21:43 AM - MS Back To Top

 Hi Ghanesh,

This helped me a lot! Thanks!

Just a question regarding the rendering of excel file. I wanted to generate it in xlsx file and not xls but whenever I put the xlsx type, the report is generated but the file cannot be opened because of an invalid file format error. Rendering the report through SSRS subscription generates xlsx file. How can I achieve this?

Thanks in advance.

 

 

 


Monday, March 13, 2017 - 3:51:07 AM - Manikanta Back To Top

This is really an Awesome solution for the environment where we don't have scope for creating data driven subscriptions. It helped me a lot.

 

Thanks,

Manikanta


Friday, February 03, 2017 - 10:05:31 AM - Manjeet Singh Back To Top

 Dear Concern,

 Just Want to know that how to run SSIS Package from stored Procedure from remoter server. I am not able to run the SSIS package and getting always network Authorty Error.

Regard's

Manjeet

 


Tuesday, January 24, 2017 - 1:49:31 PM - Howard Back To Top

 Hi Ghanesh

 

If I run it under VS 2010 , like in Part 2,  it can generate the excel file in SSIS server but if I execute it through SQL Server Management Studio, it creates an empty Excel file and I cannot even open it.  It complains about “The file is in different format than specified by the file extension.”

 

 Any idea?

 

Thanks.

 

 


Thursday, November 17, 2016 - 10:06:31 AM - suman Back To Top

 bro i did all this now i want to add id on right side top corner  of the report, how to add that column , can i edit it on report server or where?

 


Thursday, November 03, 2016 - 2:46:03 PM - Matt Back To Top

 I executed the script as you had above and the file is generating but when I open it I get a files is corrupt error message.  Once I say yes I get a problems during load screen and I have two missing files  reportingservices.css and stylesheet&version=2014.120.4100.01.  I am using vs2013 and a 2014 sql server.

 

Thanks in advance.

 


Tuesday, October 04, 2016 - 12:00:32 AM - Marcos Back To Top

Hello Ghanesh,

I am very grateful to you, this has helped me to develop a project for my company.

 

Many thanks!!!

Regards.

 


Monday, August 01, 2016 - 10:14:19 AM - Brian Back To Top

 This post helped me tremendously!!  I am attempting to modify it to write a variable to pass to SSRS but it ignores the ReadWriteVariable.

 

I have the folder destination working correctly but the variable forr SSRS will not evaluate.

 


Thursday, March 10, 2016 - 3:43:00 AM - anjaneyulu Back To Top

 

 Hi Ghanesh Prasad,

 

Greatg artical,thanks for that.

IT'S Really helpfull artical

 

Thanks and Regards

Anjaneyulu

 

 

 

 

 


Thursday, March 03, 2016 - 7:53:53 AM - Aravindh Back To Top

Hi,

 

I have used your code in my report server.The file generated properly but when try to open its showing wrong format and

Mising files : C\Reportserver\styles\Reportingservices.cs


Tuesday, February 23, 2016 - 5:24:48 PM - David Back To Top

 

Hi Ghanesh,

Thanks for sharing your solution. Generating the PDF is good but we are not able to send the output directly to a network printer.

I can do it in the past using some API calls but doing it as part of this solution, seems not flexible. 

I can still output the file to a folder and then copy it to Printer spooler. Just that not a good and complete solution.

Do you have any suggestions ?


Tuesday, January 26, 2016 - 5:58:25 AM - Rafael Dontal Goncalez Back To Top

 

 Hi Ghanesh! Great article, thanks for that. The only problem is the error I am getting:

 

This page might not function correctly because either your browser does not support scripts or active scripting is disabled.

Home > Test > CustomerInvoiceSummary

 

Your browser does not support scripts or has been configured not to allow scripts.

Report Viewer Configuration Error

 

The Report Viewer Web Control HTTP Handler has not been registered in the application's web.config file. Add <add verb="*" path="Reserved.ReportViewerWebControl.axd" type = "Microsoft.Reporting.WebForms.HttpHandler, ReportingServicesWebUserInterface, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" /> to the system.web/httpHandlers section of the web.config file, or add <add name="ReportViewerWebControlHandler" preCondition="integratedMode" verb="*" path="Reserved.ReportViewerWebControl.axd" type="Microsoft.Reporting.WebForms.HttpHandler, ReportingServicesWebUserInterface, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" /> to the system.webServer/handlers section for Internet Information Services 7 or later.

I've tried almost everything, from adding the above code to web.config file to change authentication method on SSRS. Do you have any clue on what could be done?

 

Thanks in advance.

 


Sunday, November 22, 2015 - 8:21:19 PM - First Back To Top

Hi Ghanesh,

First of all, thanks for the tips.

I just realized that the solution here is for SQL Server 2012.

 

Do you have the fix for SQL Server 2008R2 with MS Visual Studio 2008 ?

I can't compile this section

 

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _

<System.CLSCompliantAttribute(False)> _


Wednesday, November 18, 2015 - 6:55:44 AM - Ondra Back To Top

Hi, 

thank you for this article. Can you help me to modify this solution to export as many reports as I have paramteres? And what about if I have two parameters and I want to export all possibilites into separate sheet?

Ondra


Friday, August 07, 2015 - 2:56:10 PM - Alexis Back To Top

Hi, 

It is helpful because I have some request similar like this. I tried your method on my server. The file was created, but no content in the file. When I try to open the Excel file it give me warning message, saying my file in different format than specified file extention. Also the file size is 0 bytes. The script task run successful and file was created in the destination. Can you help on why the content is not there?

Thanks,

Alexis


Monday, July 20, 2015 - 1:11:34 PM - Kris Maly Back To Top

Dear Ganesh,

If your customer gives you an option of building reports from 

Report Builder

Visual Studio

Which one you prefer/pick and why?

You are knowledgeble in SSRS and looks like you have a control on the subject with lots of practical experience hence I am asking this question.

Thanls a lot in advance


Thursday, July 09, 2015 - 11:40:21 PM - Gilbert Back To Top

Hi Ghanesh,


Any feedback for this?

Thank you.

Sorry for the inconvenience.




Tuesday, July 07, 2015 - 10:51:52 PM - Gilbert Back To Top

Hi,

I figured it out how to make the url work :)

 

Hmm, can I ask for one more help? :) 

How can I make the destination to be a sharepoint site?

Not sure on how to modify this one. or should have additional things to include in the script.

 

destination = Dts.Variables("Folder_Destination").Value.ToString + "\" + "Report_" + Dts.Variables("ReportParameter").Value.ToString + "_" + Format(Now, "yyyyMMdd") + ".xls"
 
Hope for your response.
 
Thank you again. :)
 
 

Monday, July 06, 2015 - 6:57:24 AM - Gilbert Back To Top

Thank you very much for the reply.

:)

 

I will try to incorporate it with my package.

One more question. I think I can't access the url:

see below

"https://testing.com/ReportServer/Pages/Reportviewer.aspx?ItemPath=%2ftest+Services+101/ReportName&rs:Command=Render&rs:Format=EXCEL";

 

what seems the problem for this url? T_T

Thank you...


Friday, July 03, 2015 - 7:56:55 AM - Ghanesh Back To Top

Hi Gilbert,

Suppose you want to add new parameter CustomerID in this example then your url will look like this 

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

You have to make sure you use correct parameter name and modify code and solution accordingly.

Regards

Ghanesh


Friday, July 03, 2015 - 6:07:59 AM - Gilbert Back To Top

Hi Ghanesh,

 

Can you guide me by how to insert the other parameter in this URL?

 

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


Thank you.


Best Regards,

Gilbert


Thursday, July 02, 2015 - 11:15:20 AM - Ghanesh Back To Top

Thank you Gilbert for your comments.

Please refer step 4 from this tip, Let me know if still you have any doubt.

Regards,

Ghanesh


Thursday, July 02, 2015 - 7:25:05 AM - gilbert Back To Top

 

Hi,

 

How to add two or more parameters in the url?

Thank you.

 

Gilbert


Wednesday, April 15, 2015 - 9:43:19 PM - Teach Me SQL SERVER Back To Top

Hi Raghav,

Yes, you have to add each and every parameter in the url.

--

Ghanesh


Wednesday, April 15, 2015 - 3:13:24 PM - Raghav Back To Top

Thanks, its a really helpful post. What is i have multiple paramters in the report, how do i add them in the URL? Please advise


Thursday, March 26, 2015 - 4:55:39 AM - surekha Back To Top

 

hi

i need what  are reports used in ssis package with example

i need sql server job agent based on scheduling


Friday, February 13, 2015 - 10:51:21 AM - Michael Carey Back To Top

Thank you

The requirement is that the report be in Excel, not PDF, so i do not have the PDF option.

Here are a couple of ideas I am considering.

1. Select "Report" from the SSRS ribbon menu, then "Report Properties", then "Code"
Perhaps code could be written that could persist any printer settings I need when the report is rendered to Excel.

2. Execute the report via SSIS, and use the Script task to persist printer settings. For instance, I could write code that would add a button to the excel report - the button would contain script that set the printer settings I need.

3. Execute the report via Active Batch, write an "Embedded Script" task in .vbs to add a custom print button to the Excel report after it is rendered.

Have you heard of anyone who has used the "Code" window in SSRS to do something like this?


Friday, February 13, 2015 - 8:37:37 AM - Ghanesh Back To Top

Dear Vijay, Please make sure you have access to the report server and using correct report server path. 


Friday, February 13, 2015 - 7:03:53 AM - Ghanesh Back To Top

Hello Taff,

If User has access to perform any DML operation then they can perform DML operation it doesn't matter what tool they are using i.e SSMS or SSRS.

If you don't want users to perform any DML operation then change the database access permission to read only.

 

Thanks


Friday, February 13, 2015 - 6:39:48 AM - Ghanesh Back To Top

Hello Mike,

Thanks for the comment and appreciation. I have not come across to this; I would suggest you to send the report in PDF format (just for printing purpose) otherwise You can rework on the report layout so that it can work fine with the default printing of excel, I understand this will be pretty time consuming because you need to make columns much smaller (You can change column text orientation to vertical if you are displaying small length of data).

Below msdn article will help you to understand rendering behaviors of SSRS

https://msdn.microsoft.com/en-us/library/dd255234(v=sql.105).aspx#ExcelStyleRendering

https://msdn.microsoft.com/en-us/library/ee210530.aspx#RenderingExtensions

https://msdn.microsoft.com/en-us/library/dd255244.aspx

 

Thank you.


Friday, February 13, 2015 - 1:30:29 AM - Vijay Patel Back To Top

Good Topic but I am having trouble  while deploying the report

I try to configure SSRS from Configuration tool but error me out 

 

------ Build started: Project: MyReports, Configuration: Debug ------

Skipping 'Report1.rdl'. Item is up to date.

Build complete -- 0 errors, 0 warnings

========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

========== Deploy: 0 succeeded, 1 failed, 0 skipped ==========

 

Could not connect to report Server


Thursday, February 12, 2015 - 3:40:35 PM - Taff Gafoor Back To Top

 

Hello

 

i ha ve users with update access to the databases. I just installed an SSRS server for these users to generate reports. How do I prevent these users from updating or inserting data while using SSRS?

ThanksTaff

 


Thursday, February 12, 2015 - 10:40:37 AM - Michael Carey Back To Top

Hello Ghanesh,

I appreciated your article.  It was well-written and illustrated.  As it happens, I have a requirement to use SSRS to render a report in Excel, which is easy enough, but the user also wants me to cause the Excel output to conform to custom print settings without him having to manually intervene.  Do you know of a way to set Excel print settings - for example, margins = .25 all the way around - when rendering to Excel? 

Thanks, Mike

 

 


Learn more about SQL Server tools