Executing multiple ssrs reports from ssis package

Use a for loop - Get report name via variables one by one and then execute the SSRS report.

Thanks Pawan for your answer.

I use 'Execute SQL Task'  with below code;

exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='d1d1714c-2fa8-405b-a24c-9ebfaa46fec9'

Do you mean I should 3 variables for each report name (or report ID) within a for loop container?

Yes that is one way. Another simple is below if you have only 3 reports--

exec [ReportServer].dbo.AddEvent1 @EventType='TimedSubscription', @EventData='d1d1714c-2fa8-405b-a24c-9ebfaa46fec9'

exec [ReportServer].dbo.AddEvent2 @EventType='TimedSubscription', @EventData='d1d1714c-2fa8-405b-a24c-9ebfaa46fec9'

exec [ReportServer].dbo.AddEvent3 @EventType='TimedSubscription', @EventData='d1d1714c-2fa8-405b-a24c-9ebfaa46fec9'

sorry Pawan, I am a bit confused.

are youou suggesting to place above code in one 'Execute SQL Task' ?

can you please elaborate more?

Yes I think we can do that in the execute SQL task. Please try.. <<I don't have anything to test now.>>

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1d1e19e1-3c10-4a96-97d8-074b59a59cd4/ssis-execute-sql-task-component-can-it-handle-multiple-queries-?forum=sqlintegrationservices

With this you can also avoid the loop.

Hope it helps!!

and one more question, do I need to use the numbers after AddEvent

 [ReportServer].dbo.AddEvent1
 [ReportServer].dbo.AddEvent2
 [ReportServer].dbo.AddEvent3

or this is just an example

That was an example... :)

thanks pawan, I will try this and will let you know of the result.

Sure , Shall wait. :)

Hi Sazan,
Is this done? :)

Regards,
Pawan

Hi Pawan,

I have place the exec statements in one Execute Task, but it will still run all the reports at once.

What I want is to run them one by one, then the last step is to merge all report in one pdf file.

is there any other way?

Hi,
Please create three execute task one after the other and call each report from there.

Hi,
Is this done?

Hi Pawan,

I have done it, but it still run them all at once.

Ok, do one thing. After each execute task place a dummy task doing nothing.

Task1 -> Call dummytask1 (In that Just write SELECT 1 ) -> Task2 -> Call dummytask2 (In that Just write SELECT 9)  -> Task 3...

Actually what is happening is Parallel processing using threads. Once the Engine sees that they can execute things in parallel it does it to gain performance.

Hope it helps!

I will try this, and will update you.


Thanks.

Hi,
Is this done? :)

Regards,

Pawan

Hi,

not yet, but you close it.

Thanks.

I will try final suggest solution.