Saturday, February 25, 2012

pass a parameter from a SSRS report to the sql stmt in a SSIS package

How do I pass a parameter from a SSRS report to the sql stmt in a SSIS package?
Mainly need to know the correct syntax of the connection string to use for the datasource in the SSRS report. Every time I add the /SET part of the string the connection breaks.
The connection string i've been using is:
/file "C:\\PackageName.dtsx /Set \Package.Variables[StartDate];"&Parameters!StartDate.Value

Has anyone set up a SSRS report that uses a SSIS project as the datasourse with a parameter from the SSRS report used as a parameter in the SSIS sql query.

If so please post the steps.

|||

The correct syntax for expressions starts with a = followed by a string in vb format (so backslash is simply \ and a single quote as to be replaced by a double quote". For me the following example worked well:

= "-F ""C:\temp\ReportSSISSample\Integration Services Project1\PackageWithParams.dtsx""/Set \Package.Variables[Lastname];" & Parameters!filterLastname.Value

So in your case the expression should read

="/file ""C:\PackageName.dtsx"" /Set \Package.Variables[StartDate];" & Parameters!StartDate.Value

|||

Thanks,

I did get this solved on a SSIS forum post.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2052745&SiteID=1

No comments:

Post a Comment