Hi, Today my post is on ‘How to execute and render SSRS report (Excel format) from SSIS package’.
Am assuming a SSRS report (Employee Details) which accepts ‘Department’ as parameter, please have a glance at the Employee table and Report generated for particular department.
I’ll explain you how am going to execute the ‘EmployeeReport’ report from SSIS package.
Note:
‘@parameter1’ is the parameter which am passing to the report, please go through the ‘Dataset properties’ figure of SSRS, we need to use the same name in the SSIS package.
Create a SSIS package and Drag Script task into the design pane and before editing change the ‘Script language’ property to ‘Microsoft Visual Basic 2008’
Variables in SSIS package:
-
User::FileExtension - To generate report with given File extension.
-
User::FileType - File Format name of given file extension.
-
User::FullFilePath - To generate file in given file path.
-
User::Filename - To dynamically name the generated file.
-
User::Parameter1 - To pass parameter to the SSRS report.
Right click on ‘Script task’ and select ‘Edit’. Select FileExtension, FileType, FullFilepath, parameter1 as ‘ReadOnlyVariables’
After selecting ‘ReadOnlyVariables’, click on ‘Edit Script’.
Web Reference:
We need to add ‘ReportExecutionService’ as a web reference, right click on the class name and select ‘Add Web reference’ property.
Paste ‘http://localhost/reportserver/ReportExecution2005.asmx?WSDL’ in the URL box.
In the above URL localhost refers to the server, reportserver refers to the virtual directory. To know the virtual directory name, open Reporting Services Configuration manager –> click on Web Service URL then refer to the Reporting Server Web Service Virtual Directory name, as shown in below figure.
Click on GO button, we’ll get the description regarding ‘ReportExecutionService’ and click on ‘Add reference ‘ after giving some name to the Web reference(‘localhost’ in my example).
Script task Code (Microsoft Visual Basic 2008)
We need to import ‘System.IO’ class to deal with File stream objects, please go through below figure for the Script task code.
‘.LoadReport("/EmployeeReport/EmployeReport", Nothing)’ : ‘/EmployeeReport/EmployeReport’ is where the Report stored in Report Manager.
Report will be generated in the given File path with ‘Report name’ as the ‘Excel sheet name’
Let me know your comments on my Post.
Feb 28, 2012 @ 16:12:58
Hi,
This is a very great description of how to execute SSRS reports from SSIS and it covers exactly my needs I am a litte bit of a rookie into this and I get an error message when I execute the script task. Can you give me a hint to what I am doing wrong:
Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
—> System.Runtime.InteropServices.COMException (0xC0010009): The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables100.get_Item(Object Index)
at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
— End of inner exception stack trace —
at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
at ST_b20d92503be841a68d4cdc28aeae6deb.vbproj.ScriptMain.Main()
— End of inner exception stack trace —
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()”
”
Thanks in advance
Mar 01, 2012 @ 08:39:00
I fount that above error happend because of a misspelled variable in the script code.
Mar 06, 2012 @ 06:40:55
Hi Gunder, am glad that my post reached you. let me knw the further issues you faced with this approach.
Mar 16, 2012 @ 15:27:13
This is a great write up. Thanks!
I have a question: How do I pass in multiple parameters into a report?
Mar 17, 2012 @ 05:55:39
Hai Jay,
good question, you can pass multiple parameters by using the following 2 steps
step 1: declare the parameters.
With objParamParameter1
.Name = “Parameter1″
.Value = Dts.Variables(“User::parameter1″).Value //SSIS Variable
End With
With objParamParameter2
.Name = “Parameter2″
.Value = Dts.Variables(“User::parameter2″).Value //SSIS Variable
End With
Step 2: Point to a array varable and pass.
objParams(0) = objParamParameter1
objParams(1) = objParamParameter2
With objRSExec
.Credentials = System.Net.CredentialCache.DefaultCredentials
.Url = “http://localhost/reportserver/reportexecution2005.asmx”
.LoadReport(“/SSRSReport/ReportExample”, Nothing)
.SetExecutionParameters(objParams, “en-us”)
objResult = .Render(strFileType, Nothing, strFileExtension, Nothing, Nothing, Nothing, Nothing)
End With
Let me kn0w if you have any further queries.
Apr 10, 2012 @ 00:13:14
This is an amazing post. Thank you very much for the efforts.
Apr 10, 2012 @ 05:34:01
Thank you for the comment Petrica…
Apr 23, 2012 @ 16:24:15
I am still getting the error Gunder had. Can you provided sample values for the parameters? Does FileExtension need to be .xls or just xls, what should FileType be, and should FullFilePath be UNC \\server\folder or can it be C:\folder? Cheers!
Apr 23, 2012 @ 22:24:13
Hi David, FileExtension should be ‘.xls’
FileType ‘Excel’ and
FilePath C:\folder
I never tried with remote path, i’ll let you know regarding that soon…
Apr 25, 2012 @ 20:21:22
I’ve used remote paths, but you need to make sure you have all the appropriate permissions. Example: \\Some-sql12\Test\Exports\Test\2012-04-25_Report.pdf
Apr 26, 2012 @ 14:34:54
Great jay, I didn’t find an oppertunity to test that. i’ll definitly reach you I face any issue while working with remote paths, and thanks for the information.
Apr 23, 2012 @ 22:57:51
Thanks, got it working. In addition to having some variable problems I also had a typo in the script. Cheers
Apr 25, 2012 @ 20:07:00
I also have some code i use which loops through a parameter table and dynamically assigned parameters to a report. I can share the code if anyone wishes. It’s worked well and we send out a ton of reports on a daily basis.
I do have another issue though. Sometimes the script task fails due to a timeout error:
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.Net.WebException: The operation has timed out
at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)
If I manually run the report it takes over a minute to generate. Which is expected since its a 500 page report.
I am not sure how to increase the timeout of the script task ( I am not sure if SSIS has that feature).
Apr 26, 2012 @ 14:30:43
Hi Jay, have you tried setting the Timeout option of web service. In the above script task code
you can find Timeout option in objRSExec object, though I didn’t used but you’ll definitly
find that option.
or else you can change in the web config file.
May 31, 2012 @ 19:31:32
It still seems to have a timeout issue. This is the code I used:
With objRSExec
.Credentials = System.Net.CredentialCache.DefaultCredentials
.Url = “http://localhost/reportserver/reportexecution2005.asmx”
.LoadReport(“/Reports/” & strReportName, Nothing)
.SetExecutionParameters(objParams, “en-US”)
objResult = .Render(strReportFormat, Nothing, strReportExt, Nothing, Nothing, Nothing, Nothing)
.Timeout = 5000000
End With
Jun 01, 2012 @ 12:11:38
Hi Jay, Have you tried the timeout option of webservice.
Jun 28, 2012 @ 13:31:55
Hi,
Thank you for this great post.
I’ve read and only got one problem.
When I tried to execute the task, I got an error when the task tries to access the folder, it was denied. It was a System.IO part error which the task tried to write something to the specified folder.
Don’t know if you have any suggestion over this ?
Thanks.
Chi
Jul 26, 2012 @ 06:21:47
Hi Chi,
Open dcomcnfg
right click > My Computer >properties >COM Security
Under Access Permissions: Add Network Services and Users grant Local Access to each
Under Launch and Activation Permissions: Add Network Services and Users, grant Local Launch and Local Activation to each.
Give full control permission to the ‘NETWORK SERVICES’ user for the folder in which the file is being generated.
Hope this solves your issue.
Thanks
Sunil Reddy
Aug 17, 2012 @ 15:06:14
Thanks, solved with your way.
Great appreciation~
Oct 04, 2012 @ 07:32:56
Dear Sunilji,
I have tried the method you said above no luck still the same. I am suing Win Server 2008 r2 SBS 64 bit. I hope some help.
Aug 08, 2012 @ 04:00:00
Not what I was looking for but excellent anyway! Congrats!
Aug 10, 2012 @ 22:05:45
Hi,
I have created SSIS package using BIDS 2008 and added Script Task.
When I try to add ReadOnlyVariables I am getting System variables, I am not getting User variables as you have mentioned in your post.
■User::FileExtension – To generate report with given File extension.
■User::FileType – File Format name of given file extension.
■User::FullFilePath – To generate file in given file path.
■User::Filename – To dynamically name the generated file.
■User::Parameter1 – To pass parameter to the SSRS report.
Could you please help me how can i get these variables.
Its very urgent for me.
Thanks in advance
DBill
Aug 10, 2012 @ 22:06:57
Hi,
I have created SSIS package using BIDS 2008 and added Script Task.
When I try to add ReadOnlyVariables I am getting System variables, I am not getting User variables as you have mentioned in your post.
■User::FileExtension – To generate report with given File extension.
■User::FileType – File Format name of given file extension.
■User::FullFilePath – To generate file in given file path.
■User::Filename – To dynamically name the generated file.
■User::Parameter1 – To pass parameter to the SSRS report.
Could you please help me how can i get these variables.
Its very urgent for me.
Thanks in advance
Meera
Aug 13, 2012 @ 03:52:06
Someone essentially help to make critically articles I’d state. This is the very first time I frequented your website page and so far? I surprised with the research you made to make this particular put up incredible. Excellent activity!
Aug 13, 2012 @ 04:04:37
Would you be able to do anything far more advanced
Aug 13, 2012 @ 22:42:26
An individual got what beyond the jaws but I really could not need expressed the item much better than an individual, Chapeau for this!
Aug 14, 2012 @ 22:01:30
I am trying to created SSIS package for bulk upload of my ssrs reports.
But I am not getting below variables in ssis package, i have all system ::….variables.
Variables in SSIS package:
■User::FileExtension – To generate report with given File extension.
■User::FileType – File Format name of given file extension.
■User::FullFilePath – To generate file in given file path.
■User::Filename – To dynamically name the generated file.
■User::Parameter1 – To pass parameter to the SSRS report
Could you please let me know how can i get all these user variables?
Appreciate your help.
Aug 15, 2012 @ 12:59:38
Hi, Create those variables in your SSIS packages with the scope of your Package or script task, than you can find those variables in that tab. Check for the variables with the naming convention as user::
Thanks for visiting my blog, hope it will help you now.
let me know if the issue solved.
Aug 17, 2012 @ 16:27:30
Thank you Sunil, I am able to create variables.
Appreciate your help.
Aug 18, 2012 @ 20:33:29
I do agree with all of the ideas you’ve presented in your post. They are very convincing and will definitely work. Still, the posts are very short for novices. Could you please extend them a bit from next time? Thanks for the post.
Aug 19, 2012 @ 09:26:56
I am also appreciating this blog info, thanks for giving the suitable example…..!
Sep 20, 2012 @ 02:22:56
Do you mind if I quote a few of your posts as long as I provide credit and sources back to your
webpage? My website is in the exact same niche as yours and my visitors would genuinely
benefit from a lot of the information you provide here. Please let
me know if this ok with you. Regards!
Sep 26, 2012 @ 13:45:16
I’m trying to use this to run an excel report from Visual Studio 2010 SSRS from SSIS. Would this work?
I just ran it and got the following message:
at System.RuntimeMethodHandle._InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeType typeOwner)
at System.RuntimeMethodHandle.InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeType typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
Sep 27, 2012 @ 15:02:47
Could be a path problem, when you are moving the file, the source only has the source file name. It should have the complete path.
Can you catch the inner exception and let me know.
Nov 02, 2012 @ 08:49:07
if i dont have any parameter in my report then…in that case what is the script..for call ssrs report in ssis..????
Dec 19, 2012 @ 06:57:51
if you don’t want to use Report Parameters, you can exclude the content related to parameters in the Script. But my suggestion is not to follow the above mentioned approach if you don’t have parameter. There are other best ways to which will do the same.
Nov 13, 2012 @ 11:49:18
That is absolutely what I’ve been looking for…!! THANK YOU..
Dec 19, 2012 @ 06:51:32
Glad that my BLOG reached you, thank you too for visiting…
Nov 29, 2012 @ 19:42:13
great post. But I have a problem which the script runs sucessfully when it runs inside the Visual Studio, but cannot run the SSIS package in SQL agent or double click on the SSIS file to execute, didn’t see detail error, just says failed.
Dec 19, 2012 @ 06:54:07
Hi, without going through the error details I can’t comment. Can paste the error message at it is over here…
Dec 19, 2012 @ 21:55:51
Hi, I am unable to save the report to local. I gave explicit permissions to the folder to “NETWORK SERVICE”. Error message says unauthorized access.
Dec 20, 2012 @ 17:58:56
Hi Sudhakar, As discussed above(in a response to Chjquest), giving explicit permission to NETWORK SERVICE user for the folder (in which you’ll need to generate report) should solve the issue.
Can you give further information on error message.
Jan 17, 2013 @ 13:15:34
My requirement is after certain load in SSIS,I want to add this task and after It i want to subscribe it to go in mail.
What Should be done in addition to this to subscribe the report?
Jan 17, 2013 @ 19:22:59
Hi, You have two ways to implement.
1. Use the above task and render the report in required format. Use system procedure to send E-mail with file attachment.
[USE msdb]
GO
EXEC sp_send_dbmail
@profile_name=,
@recipients=,
@subject=,
@body=,
@file_attachments =
as already hold filename in a variable, pass it to above system procedure.
Note: Database Mail profile is pre-requisite.
2. when you create subscription for a SSRS report, a job will be created automatrically which will trigger on given schedule time. To find the details of the job, use the following query.
SELECT c.Name AS ReportName, c.Path AS Report_Path,
rs.ScheduleID AS JOB_NAME, s.[Description], s.LastStatus
, s.LastRunTime,c.CreationDate,c.ModifiedDate
FROM ReportServer..[Catalog] c
JOIN ReportServer..Subscriptions s ON c.ItemID = s.Report_OID
JOIN ReportServer..ReportSchedule rs ON c.ItemID = rs.ReportID
AND rs.SubscriptionID = s.SubscriptionID
After the extraction in SSIS use ‘Execute SQL Server Agent Job Task’ to trigger the job.
Let me know if it helps.
Thanks…
Sunil Reddy
Mar 06, 2013 @ 20:40:37
Hi All,
I am facing some problem after successfully creating the file in given path. The problem is, the excel file which is created from SSRS report is been used by SSIS script task and we are unable to use the same file while sending the email.
Just for confirmation:
we have used the CLOSE, FLUSH and DISPOSE option of file stream object to release the process but we are not having any luck…
any suggestion to solve our problem.
Thanks..
Kishore
Apr 25, 2013 @ 15:22:32
I’m really enjoying the design and layout of your site. It’s a
very easy on the eyes which makes it much more pleasant for me to come here and visit more often.
Did you hire out a developer to create your theme?
Great work!
Jun 12, 2013 @ 19:08:09
Hi Sunilreddy
Really great post I am relatively new to the VBScripting thing, this is exactly what I am looking but the only difference is that I want to email the report and not save it to a file location, do you have some suggestions.
Jun 14, 2013 @ 11:10:41
Hi Wayne, create a Data driven subscription for that report and use the job of that subscription.