How to pass a parameter to SSRS report from SSIS package?

Question

Hello,

I'm writing my first SSIS pkg and I'm stuck. Any insight would be greatly appreciated.

• I'm running a sql agent job that kicks off a SSRS report. (The job was generated via a scheduled subscription.)

• This report relies on 2 stored procs which require the parameter 'When' (date type) and it dumps a PDF of the report to a file share.

• My execute sql task runs this: EXEC msdb.dbo.sp_start_job N'myJobName';

How can I pass a value for the parameter 'When' into the report?

Thanks,

Trey Carroll

Answer 1

Why do you even need SSIS for this? Set up a subscription in SSRS to export the report where you need in the format you need on whatever schedule you want. The Subscriptions in SSRS allow for parameters to be stored with the subscription as well.

Answer 2

I figured out how to accomplish this.

When you define the report in SSRS turn on "Report Data" view. On this view you will find a Folder named Parameters. Expanding this shows a node for each parameter. Right click and view parameter properties.

I solved my problem by selecting default values > Get Values from a query (specify Dataset and field). I set up a new db table named ReportParams.

CREATE TABLE ReportParams ( ParamName varchar(50), ParamVal varchar(50) )

This allowed me to do a

SELECT top(1) ParamVal FROM ReportParams WHERE ParamName = 'myparamname'.   

By having my SSIS job inseRt the parameter name and value into this table, I'm able to pass a parameter to the SSRS report's default value.

Okay, it's somewhat kludgy, but it works. If anybody has a cleaner way to get this done, I'd love to hear it.

William - the SSIS job is necessary to fill the database with the data that drives the DataSets that the Report depends on.