Starting SSIS 2012 Job Remotely

Scenario: Inspired by the new SSIS 2012 capabilities, you’ve managed to convince management to install SSIS 2012 on a new server for new ETL development. But your existing ETL is still on SQL Server 2008 (or R2) and there is no budget for migration and retesting. You want to start SSIS 2012 jobs from the 2008 server, e.g. in SQL Server Agent on completion of a certain SSIS 2008 job.

Solution: Courtesy to Greg Galloway for clueing me on this, thanks to its CLR integration, SSIS 2012 supports initiating jobs via stored procedure in the SSIS catalog. Although the process could benefit from simplification, it’s easy to automate it, such as (you guessed it) with an SSIS 2008 calling package. It goes like this:

1. Call catalog.create_execution in the SSISDB database to create an execution for the SSIS job. At this point, the job is not started. It’s simply registered with the SSIS 2012 framework. Notice that you want to get back the execution id because you will need it for the next calls.

EXEC catalog.create_execution

@folder_name=N'<SSIS Catalog Folder>’,

@project_name=N'<project name>’,

@package_name=N’package name’,

@reference_id=NULL,

@execution_id=? OUTPUT

Note: If you use SSIS 2008 and you declare the variable that stores execution_id (which is a big integer) to be Int64, SSIS 2008 would probably choke due to a known issue with big integers. As a workaround, change the SSIS variable type to Int32.

1. So that the SSIS 2008 job waits for the SSIS 2012 job to complete, set the Synchronous parameter.
   EXEC catalog.set_execution_parameter_value @execution_id = ?

   ,@object_type=50 ,@parameter_name=’SYNCHRONIZED’

   ,@parameter_value=1

2. Now you are ready to start the job by calling catalog.start_execution and wait for it to finish.
   EXEC catalog.start_execution @execution_id = ?

• To get the back the job status, query the status field from the [catalog].[executions] view. A status of 4 means that the job has failed.
  SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id = ?

• To get the actual error, query the [catalog].[event_messages] view, e.g.:
  SELECT TOP 1 cast([message] as nvarchar(500)) as message, cast ([execution_path] as nvarchar(500)) as execution_path

  FROM [catalog].[event_messages]

  WHERE operation_id = ? AND event_name = ‘OnError’

  ORDER BY message_time DESC

Tip: If you use SQL Server Agent to start the SSIS 2008 job, use a proxy account to execute the job under a Windows service account that has the required permissions to the SSISDB catalog to start SSIS 2012 jobs.