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

4 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

  3. sneska said

    I have a need to send payslip to every employee via email on demand (it is not determined weather it is 1. or 3. day of the month..).The report in SSRS is the same,
    it depends on input parameters @Godina,@Mesec,@Obracun,@IDTipObracuna,
    but the range of employees to whom email will be sent varies.
    I have created data-driven subscription, with only ones schedule, so I can start a job manuely. Difficulty is that I find out that I have to create 5 data driven subscriptions for 5 various possibiltities of @IDTipObracuna (1,2,3,4 or 5).
    e.g
    exec [dbo].[SelektujRadnikeZaSlanjeListicaNaEmailPARAMETRIZOVANO] @Godina=2015,@Mesec=3,@Obracun=1,@IDTipObracuna=1
    exec [dbo].[SelektujRadnikeZaSlanjeListicaNaEmailPARAMETRIZOVANO] @Godina=2015,@Mesec=3,@Obracun=1,@IDTipObracuna=2
    exec [dbo].[SelektujRadnikeZaSlanjeListicaNaEmailPARAMETRIZOVANO] @Godina=2015,@Mesec=3,@Obracun=1,@IDTipObracuna=3
    exec [dbo].[SelektujRadnikeZaSlanjeListicaNaEmailPARAMETRIZOVANO] @Godina=2015,@Mesec=3,@Obracun=1,@IDTipObracuna=4
    exec [dbo].[SelektujRadnikeZaSlanjeListicaNaEmailPARAMETRIZOVANO] @Godina=2015,@Mesec=3,@Obracun=1,@IDTipObracuna=5

    Each fifteen days I willl have to modify all 5 subscriptions with new input parameters @Godina=2015,@Mesec=3,@Obracun=2, then @Godina=2015,@Mesec=4,@Obracun=1 …
    e.g.
    exec [dbo].[SelektujRadnikeZaSlanjeListicaNaEmailPARAMETRIZOVANO] @Godina=2015,@Mesec=3,@Obracun=2,@IDTipObracuna=1
    exec [dbo].[SelektujRadnikeZaSlanjeListicaNaEmailPARAMETRIZOVANO] @Godina=2015,@Mesec=3,@Obracun=2,@IDTipObracuna=2
    exec [dbo].[SelektujRadnikeZaSlanjeListicaNaEmailPARAMETRIZOVANO] @Godina=2015,@Mesec=3,@Obracun=2,@IDTipObracuna=3
    exec [dbo].[SelektujRadnikeZaSlanjeListicaNaEmailPARAMETRIZOVANO] @Godina=2015,@Mesec=3,@Obracun=2,@IDTipObracuna=4
    exec [dbo].[SelektujRadnikeZaSlanjeListicaNaEmailPARAMETRIZOVANO] @Godina=2015,@Mesec=3,@Obracun=2,@IDTipObracuna=5
    and after fifteen days
    exec [dbo].[SelektujRadnikeZaSlanjeListicaNaEmailPARAMETRIZOVANO] @Godina=2015,@Mesec=4,@Obracun=1,@IDTipObracuna=1
    exec [dbo].[SelektujRadnikeZaSlanjeListicaNaEmailPARAMETRIZOVANO] @Godina=2015,@Mesec=4,@Obracun=1,@IDTipObracuna=2
    exec [dbo].[SelektujRadnikeZaSlanjeListicaNaEmailPARAMETRIZOVANO] @Godina=2015,@Mesec=4,@Obracun=1,@IDTipObracuna=3
    exec [dbo].[SelektujRadnikeZaSlanjeListicaNaEmailPARAMETRIZOVANO] @Godina=2015,@Mesec=4,@Obracun=1,@IDTipObracuna=4
    exec [dbo].[SelektujRadnikeZaSlanjeListicaNaEmailPARAMETRIZOVANO] @Godina=2015,@Mesec=4,@Obracun=1,@IDTipObracuna=5

    e.g. I want my command or query that returns a list of recipients and optionally returns fields used to vary delivery settings and report parameter values for each recipient look like this:
    exec [dbo].[SelektujRadnikeZaSlanjeListicaNaEmailPARAMETRIZOVANO] @Godina=2015,@Mesec=3,@Obracun=1,@IDTipObracuna=5

    Is it possible to create SSIS package to automatize this proccess?

    • Yes sneska, It is possible to automatize the process in two possible ways.
      1. creating SSIS package and calling SSRS report (you can pass parameters from a table).
      2. Creating Data Driven Subscription for the SSRS report (you can pass parameters from a table).

      Thanks
      Sunil Kumar

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 664 other followers

%d bloggers like this: