I know the title of this post is a little cryptic and something that only a techie would like but let me give you the real world requirements so that is will be a little more concrete.

Solution requirements:

1. User uploads file to SharePoint 2010
2. On file upload  an Event Handler executes a SSIS package
3. SSIS package loads aggregate data into a database for SSRS Reports
4. Upon completion of SISS package, a SSRS  Subscription executes SSRS reports (in SharePoint Integrated mode) which  get new  data from the database
5. Subscription delivers reports (email, shared folder) and Event handler continues with some other tasks

Sounds simple enough right, but there a few challenges

1. SSIS is not Event Driven

This is by design, because in most cases SSIS is used to ETL large amounts of data. Executing SSIS packages can have a big impact of IT environment resources (think night ETL process for Enterprise Data Warehouse).

However, there are several ways to execute a SSIS package outside of its schedule. DTEXEC can do it but for security reasons in this situation I prefer to use the sp_start_job  System Stored Procedure.

1. sp_start_job permissions

Make sure that the user/services account that executes sp_start_job has the correct permissions:

By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:

• SQLAgentUserRole
• SQLAgentReaderRole
• SQLAgentOperatorRole

For details about the permissions of these roles, see SQL Server Agent

Fixed Database Roles.

Members of SQLAgentUserRole and SQLAgentReaderRole

can only start jobs that they own. Members of SQLAgentOperatorRole can start all local jobs including those that are owned by other users. Members of sysadmin can start all local and multiserver jobs.

1. sp_start_job is asynchronous

Although is it not officially documented by Microsoft, sp_start_job is asynchronous which means when the sp_start_job executes it immediately

returns that the SSIS package was executed successfully. Per the requirements above, this will cause the subscription to generate the reports before the SSIS package has staged the new data into the database.

sp_start_job can be  synchronous by simply adding this code to a  stored procedure that calls sp_start_job.

1. Dynamic SSRS  Subscription

I am not referring to Data Driven Subscritpions. Now the SSIS package can be executed synchronously, I can be sure that the reports have fresh data and that the Event Handler can continue with some other tasks that are dependent on the SSIS package.

I could simply create an Execute SQL task in the SSIS package which uses sp_start_job to run the subscription but there are few issues with this.

When a subscription is created for the SSRS reports (The reports in this case are deployed to SharePoint). The SQL agent job which executes the subscription gets a GUID for its name

I don’t want to have to hard code the job GUID in my SSIS package. That would also add more steps to my deployment to production. And if the subscription is ever modified it could cause the whole process to fail.

If you open up the step which executes the subscription you will see the following:

The exec [ReportServer].dbo.AddEvent schedules the subscription to be run but to get the exact subscription for the report requires a little more work.

Notice that the @EventData = … is the GUID for the subscription.

But the subscription GUID needs to be associated to the correct report. To get that use the following query in the ReportServer DB:

select *

FROM
[ReportServer].[dbo].[Subscriptions]

In the Results plane Expand the ExtensionSettings field and you will see something like the following

<ParameterValues><ParameterValue><Name>FILENAME</Name><Value>Weekly_Summary_Analytics</Value></ParameterValue>…

Now the file name from the ExtensionSettings can be associated to the Subscription GUID and the correct subscription can be executed without hard coding GUIDs.

Just add the following code to an Execute SQL task that is connected to the ReportServer DB.

declare @GUID uniqueidentifier = (SELECT top 1
[SubscriptionID]

  FROM [ReportServer].[dbo].[Subscriptions]

  where ExtensionSettings like ‘<ParameterValues><ParameterValue><Name>FILENAME</Name><Value>Weekly_Summary_Analytics</Value></ParameterValue>%’)

exec [ReportServer].dbo.AddEvent @EventType=’TimedSubscription’, @EventData= @GUID

This assumes that you have unique file names/ SSRS reports.