Sunil Reddy Enugala

…..is just walking through MSBI Street.

Execute SSRS report from SSIS package Part#2

Posted by Sunil Reddy Enugala on October 16, 2013

image

Hi, Earlier we have discussed on execution of SSRS Report from SSIS package. I hope it reached most of the people as it shows in the stats of my blog.

In my Today post, let us discuss regarding another approach of Executing SSRS Report. This approach will use Subscriptions(Standard/Data-driven) of SSRS.

If you have goon through my last post on Standard Subscription, I have shared a script which gets list of subscriptions created on that server.

Please find that same script below again.

   1: SELECT R.ScheduleID AS JobName   

   2:     ,C.NAME AS ReportName   

   3:     ,C.[Path] AS ReportPath   

   4:     ,S.SubscriptionID   

   5:     ,SUBSTRING(S.DeliveryExtension   

   6:              , 14   

   7:              , len(S.DeliveryExtension))    

   8:              AS DeleveryType   

   9:     ,S.[Description] AS Subscription_Desc  

  10:      ,S.LastStatus AS Subscription_Exec_Status  

  11:      ,C.CreationDate  

  12:      ,C.ModifiedDate  

  13:      ,S.LastRunTime  

  14:  FROM dbo.[Catalog] C  

  15:  INNER JOIN dbo.Subscriptions S   

  16:  ON S.Report_OID = C.ItemID  

  17:  INNER JOIN dbo.ReportSchedule R   

  18:  ON R.SubscriptionID = S.SubscriptionID  

  19:  --WHERE C.NAME = 'ReportsExecutionLog'   

  20:  --Pass Report Name as C.NAME

If you run the above query, the result would look like below figure.

image

I am using Data Driven Subscription which I have created/discussed in my last post, as I can pass Parameter values to the report.

As we discussed, If you create Subscriptions in SSRS, a SQL Server Job will be created in the given instance of SQL Server to

Execute the report on scheduled time.

Please go through the below figure, where you can find the SQL Server Jobs with the names resulted from the above Query.

image

These Subscription Jobs will execute my EmpDeptReport Report, for the given scheduled time while creating the subscriptions.

In my last post on Data Driven Subscription I have created two SQL Server tables, to pass the Input parameter values to both

E-Mail and FileShare type of Data Driven Subscriptions. Please go through the below figure to find the Schema.

image

 

We can use above SQL Jobs and tables to execute the EmpDeptReport Report, from a Stored Procedure / SSIS Package.

Please find the below script to execute a SQL Job.

   1: -- Exeecute the below script to start a job.  

   2: USE msdb ;

   3: GO

   4:  

   5: EXEC dbo.sp_start_job N'A151B953-4A14-4B91-83A7-377ED5451E12' ;

   6: GO

I have changed the Report parameter value in the Subscription input table and used the above script to deliver EmpDeptReport Report into a FileShare path.

image

Please go through the below figure, as soon as I executed the Subscription Job, EmpDeptReport Report was rendered in Excel format into the Shared path folder.

image

Similarly you can use the E-Mail Data Driven Subscription Job to deliver the report for given Report parameter value.

Please send me the issues/queries you have while following the above approach.

All the Best..  Smile

- Sunil Reddy Enugala

About these ads

2 Responses to “Execute SSRS report from SSIS package Part#2”

  1. This is really great series all together Sunil. Thanks for sharing all with code !

  2. Anonymous said

    its very good brother

Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 659 other followers

%d bloggers like this: