Monitor, Run SSIS Package using Stored procedure / T-SQL

Introduction

In this post, you will learn how to Monitor and Run SSIS Package using Stored Procedure (T-SQL Code) – Packages stored in SSIS Catalog.  Calling SSIS Packages via T-SQL / Stored procedure code can be useful if you want to trigger SSIS package execution from your SQL code on certain conditions (i.e. Load data from SSIS Package before executing certain SQL code).

Techniques mentioned in this article applies to SSIS PowerPack or Native SSIS Packages.  Now let’s look at each piece step-by-step.

How to run SSIS package using Stored Procedure / T-SQL Code

Let’s look at simple example of Calling SSIS package deployed to SSIS Catalog. Basically, there are three steps (setting parameters is optional).

  1. Required – Call catalog.create_execution System stored proc – This Creates new execution and returns you execution_id which can be saved to a variable
  2. Optional – Call catalog.set_execution_parameter_value System stored proc – Call this multiple times if you want to set parameters
  3. Required – Call catalog.start_execution to execute the package

Now let’s look at the example. Below example is the simplest way to call SSIS package using SQL code. Below code creates a new SSIS execution and returns immediately without waiting to finish package execution (asynchronous execution).

How to wait until SSIS package is finished (Set SYNCHRONIZED)

By default SSIS Catalog Package execution is in asynchronous mode. So what if you really want to wait until SSIS package execution is done (Synchronous mode) ? Just add one more sored procedure call before calling catalog.start_execution (see below)

 

Set SSIS Package Parameters via SQL Code for execution

Now lets look at common scenario to pass package parameters.

How to set SSIS environment for package execution using SQL Query

Many times you have need to select SSIS Environment rather than setting individual SSIS Parameters (e.g. Use DEV Environment parameters). See below code How to find correct reference_id for SSI environment  and pass to create_execution  stored procedure call.

Here is how you set SSIS environment using SSMS UI. In the next section see the code to select same via T-SQL code.

 

Set SSIS Environment for Package Execution - SSMS UI

Set SSIS Environment for Package Execution – SSMS UI

 

Setting SSIS Environment for execution using SQL Code

 

How to get last SSIS Package execution Status using SQL Query

Now let’s look at, how to get SSIS package execution Status for last execution using SQL Query.  In this example we are grabbing last package execution_id using Max(execution_id) function but you can hardcode execution_id too.

See different numeric status codes and description for SSIS Package Execution.

 

How to get last SSIS Package execution log using SQL Query

Now let’s look at full SQL Query which can give us execution status, errors and full execution log of last SSIS Package execution.

Here is the screenshot. Notice how we have message_xml column to see more than 8000 characters from log message in your SSMS output (Grid mode). SSMS can show maximum 8000 characters in one cell so using try_Catch function along with XML datatype will fix that issue.

See SSIS Package Execution log using SQL Query (T-SQL code) - View Execution Status, Error Messages, Information - Also add more than 8000 characters fix for SSMS grid

See SSIS Package Execution log using SQL Query (T-SQL code) – View Execution Status, Error Messages, Information – Also add more than 8000 characters fix for SSMS grid

Full SQL Script (Execute SSIS Package / Monitor SSIS Package)

Here is the full script.

Conclusion

So finally to conclude, you saw that how easy it is to execute SSIS packages using SQL code, monitor SSIS package execution and extract valuable log via. SSIS PowerPack comes with additional logging features along with standard logging. Try SSIS PowerPack to get additional 70+ new SSIS tasks and components in your SSIS toolbox.

 

Posted in SSIS PowerPack and tagged , .