Run SSIS job from SSRS report button?

Question

I'm probably sure it can't be done, but I'll check with you guys anyways.

We have and SSIS job and an SSRS report. The SSIS job generates data from a remote system to our local server; the SSRS report reports on the local data generated from the SSIS job.

The data coming from the SSIS job changes countless times daily, but the SSIS job only updates hourly. So let's say that the job ran at 7:01AM and a user generates the report at 7:50AM. There's a good possibility that the data may have changed in those 49 minutes.

So I had two options: 1. Execute the SSIS job from the stored procedure used for the report. So when I invoke the report, the SP will first execute the SSIS job and update the data, then it will do the SELECT statement that returns the report data. The problem here is that the SSIS job takes a few seconds, so it will seem that the report's slow.

2. Add some button to the SSRS report that will execute the SSIS job. That way, if the user gets obsolete results in the report, he can invoke the SSIS job and run the report again.

So I'm leaning towards the 2nd one, but I don't know if I can add another button to the report that will execute an SSIS job. I also want to use SSRS for this (ie. not create a webform that displays the report).

Any help is appreciated.

Answer 1

Talking about the second choise, you can create a @RunJob parameter maybe Boolean type, then in dataset validate if user has selected True for running the job. You should set default value to False to avoid every time the report is generated the job be executed.

In the dataset query use sp_start_job stored procedure to run the job that execute your SSIS packages if the parameter @RunJob is True, otherwise just retrieve the report data.

While this can help you to get what you need, I don't recommend you to use SSRS for your purpose since it is not designed to affect anyway the underlying database.

Let me know if this can help you.