Home > SSRS Expert > SSRS #42 – Kick off the Timed Subscription Right After Your ETL Process is Finished

SSRS #42 – Kick off the Timed Subscription Right After Your ETL Process is Finished

You have your SSRS report online, but business users want to receive the report as Excel file attachment in an email as early as possible in the morning.

Creating the Timed Subscription on the reporting server is simple. Even if you’ve never done it before, it’s simple enough that you don’t need instructions to do that.

Kicking off the timed subscription in your ETL process or in a SQL Agent job, however, is not a very well known task. The assumption here is that you have a daily ETL process already in place to refresh the data for your report. Now the challenge is to kick off that SSRS subscription as soon as your ETL process is finished.

Here are some simple steps.

Step 1: create a subscription for your report on the reporting server.

Here are two subscriptions I’ve created for my report, each subscription having different report parameters.

image

  Here is one of the subscriptions. Note that the schedule is a dummy schedule, because the subscription will be not be kicked off by clock. It will be kicked off from your ETL process or from a SQL agent job step.

image

Step 2: To kick off the subscription, here is the command you would use.

EXEC [myRSServer].reportserver.dbo.AddEvent @EventType=’TimedSubscription’,@EventData=’myScriptionID1′
EXEC [myRSServer].reportserver.dbo.AddEvent @EventType=’TimedSubscription’,@EventData=’myScriptionID2′

myRSServer: this will be your reporting services server

myScriptionID1: this is the subscription ID for the timed subscription you’ve created in step 1. In step 3 I’ll show you how to find the subscription ID.

If you have the linked server set up for your reporting services server, you can just execute the above SQL command in a SQL agent job right after your ETL job step. If you don’t need the linked server, you can include the above commands right within your SSIS package.

Step 3: run this query to find the subscription IDs

image

Armed with the above knowledge, you are now ready to automate your SSRS reports and deliver them right to your business users’ inbox with refreshed data daily.

  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: