Execute SSIS Package from Stored Procedure in SQL Server

Problem

Our organization has been using SSIS packages to perform all sorts of operations that are tied directly to our back-end databases. We would like the ability to leverage these capabilities directly from our applications. Typically, we build SSIS packages, deploy them to the SSIS catalog, and execute the packages from SQL Server Agent jobs based on a schedule. While this works well, we want to be able to execute SSIS packages from an application and specify the values for the package parameters at runtime.

As an example, we provide a cloud-based CRM application. We want to roll out a new customer onboarding process which will be completely self-service. When a customer signs up for our service, they need to get data from their existing system loaded into our application. We will provide a web interface where a customer can load their data via simple CSV files. The web application will allow the customer to navigate to a CSV file and upload the file. The upload will place the file in a folder on our server, and the web application will execute an SSIS package to perform the load.

How can we accomplish this?

Solution

SQL Server Integration Services (SSIS) provides the SSISDB database which includes stored procedures and views to allow you to work with SSIS projects and packages deployed to the Integration Services Catalog. The approach I will take to accomplish your goal is to create SQL objects that provide the ability to execute an SSIS package from the catalog by using a stored procedure. The stored procedure will leverage objects in the SSISDB database as well as some supporting objects.

There is great benefit to this approach. You already have existing SSIS packages that can be executed. By executing these SSIS packages at various points in your application, you can reuse the existing processes.

As a reference, take a look at the earlier tip How To Execute an Integration Services (SSIS) Package from a SQL Server Stored Procedure. This tip showed a simple example of executing an SSIS package via a stored procedure. It did not go into the detail of handling project and package parameters. The tip walked through the ADO.NET code required to execute a stored procedure. In this case I will provide the demo from the SQL Server developer’s perspective, focusing on the T-SQL required to execute the stored procedure.

The following is the outline I will follow to implement this solution:

• Review the key points of an SSIS package that is deployed to the catalog
• Review the SSIS catalog after the SSIS project and package are deployed to the catalog
• Do a detailed walk through of how to create a stored procedure that can execute an SSIS package deployed to the SSIS catalog
• Demonstrate executing the stored procedure using simple T-SQL and walk through the SSIS catalog report executions to see the details of how it all works

SSIS Package Review

Our starting point will be to review the SSIS package that will be used in the demo. The focus will be on the parts of the package that are relevant to deployment in the SSIS Catalog and executing the package from the catalog. The package is very simple. It loads a CSV file from a folder into a SQL Server table.

SSIS has project parameters and package parameters in order to make the SSIS package reusable. Project parameters are ones that apply to multiple SSIS packages in the project. Package parameters are values that are specific to an individual SSIS package. The SSIS catalog provides the capability to store and update the parameter values as well as specify them when we execute a package.

You can see the Project Parameters (Project.params) in the Solution Explorer in Visual Studio (or SQL Server Data Tools) underneath the SSIS project as shown below:

Double-click on the Project.params node to display the project parameters: 

 

The main points are:

• CRM_Flat_File_Folder is the folder where the SSIS package will check for a CSV file to load into a SQL Server table. Normally this would be a UNC path (e.g. \\SERVERNAME\FOLDERNAME) but for demo purposes I’m just using a folder on my local machine. Note that the folder is accessed on the server where SQL Server is running which is my local machine in this case.
• CRM_Staging_ServerName is the server name where the SQL Server instance is running. Normally this would not be the local machine but for demo purposes I am using my local machine.
• Both project parameters have the Required property set to True. This is an indicator that we should be supplying a different value when we deploy to the SSIS Catalog. The project parameter values in the SSIS package are used when developing and testing the package.

You can see the package parameters by clicking on the Parameters tab in the SSIS package designer. The sample package has the following package parameter:

The package parameter is used to specify the actual file name to be loaded. This will typically vary each time the package is executed. The value shown above is used when creating and testing the package. The Required property is True to indicate that a value needs to be specified when the package is deployed to the SSIS Catalog and typically when the package is executed from the catalog. For example, when you execute an SSIS package from the catalog in a SQL Agent job step, you will be forced to enter a value for a required package parameter.

SSIS Project Catalog Deployment

Now that we have reviewed the sample SSIS package, I will review the SSIS project after it is deployed to the Integration Service Catalog. There are a couple of steps involved in creating the Integration Services Catalog, deploying the SSIS project to the catalog, and creating the environment. Take a look at the tips SSIS Project Deployment Model Part 1 of 2 and SSIS Project Deployment Model Part 2 of 2 to get the details.

Open SQL Server Management Studio (SSMS) and navigate to the Integration Service Catalogs in the Object Explorer as shown below:

The following are the main points:

• CRM is the folder name where the project is deployed
• CRM_OnBoarding is the name of the SSIS project
• LoadCustomers.dtsx is an SSIS package in the SSIS project; I will execute it in the demo
• DEFAULT is the name of an environment that contains values for the project parameters

The environment has a list of variables and their values. Double-click on DEFAULT and the variables and values will be displayed as shown below:

The project parameters were defined in the SSIS package. Typically, you use different values than what was specified in the package. Since this is just a demo I did not. However, the benefit of using an environment is that if any of the values need to change, you can update them in the environment rather than changing the SSIS package. Any package in the SSIS project can use the environment variable values at runtime.

To see how the environment is connected to the project, right-click on the CRM_OnBoarding project and select Configure from the context menu. The Configure window is shown below after clicking References (you have to add a reference to the environment that you want to use in the SSIS project):

Once a reference to the environment is added to the project, you can map the environment variables to the project parameters. Click Parameters and you will see the mapping:

When the Value column shows a variable name with an underline, the value comes from the environment variable with that name. You can have a reference to more than one environment. Whatever environment you use at runtime, the mapping will be performed.

The Customer_Flat_File_Name is a package parameter, so the value shows the file name to use rather than a mapping to an environment variable. I will show how to specify the value when you execute the SSIS package from a stored procedure.

Create a Stored Procedure to Execute the SSIS Package

At this point we are going to walk through a stored procedure that will execute the SSIS package deployed in the Integration Services Catalog. The stored procedure is shown below:

CREATE PROCEDURE [dbo].[ExecuteLoadCustomersSSISPackage]
    @CUSTOMER_FLAT_FILE_NAME   NVARCHAR(50)
,   @EXECUTION_ID              BIGINT OUTPUT
AS
BEGIN
   -- 1. declare variables
   DECLARE
       @PROCEDURE_NAME  SYSNAME
   ,   @FOLDER_NAME     NVARCHAR(128)
   ,   @PROJECT_NAME    NVARCHAR(128)
   ,   @PACKAGE_NAME    NVARCHAR(128)
   ,   @REFERENCE_ID    BIGINT
 
   -- 2. get this stored procedure's name
   SET @PROCEDURE_NAME = OBJECT_NAME(@@PROCID
 
   -- 3. get the parameters for executing the SSIS package
   SELECT 
       @FOLDER_NAME  = [FOLDER_NAME]   
   ,   @PROJECT_NAME = [PROJECT_NAME]   
   ,   @PACKAGE_NAME = [PACKAGE_NAME]  
   ,   @REFERENCE_ID = [REFERENCE_ID]
   FROM [dbo].[ProcedureToCreateExecutionMapping]
   WHERE [ProcedureName] = @PROCEDURE_NAME
 
   -- 4. check if 1 row was returned
   IF @@ROWCOUNT <> 1
   BEGIN
      -- throw error
      RETURN
   END
 
   -- 5. create the package execution
   EXEC [SSISDB].[catalog].[create_execution]
       @folder_name = @FOLDER_NAME
   ,   @project_name = @PROJECT_NAME
   ,   @package_name = @PACKAGE_NAME
   ,   @reference_id = @REFERENCE_ID
   ,   @execution_id = @EXECUTION_ID OUTPUT
 
   -- 6. set value for Customer_Flat_File_Name package parameter
   EXEC [SSISDB].[catalog].[set_execution_parameter_value]
        @execution_id = @EXECUTION_ID  
    ,   @object_type = 30 -- package parameter 
    ,   @parameter_name = N'Customer_Flat_File_Name'  
    ,   @parameter_value = @CUSTOMER_FLAT_FILE_NAME
   
   -- 7. start the execution
   EXEC [SSISDB].[catalog].[start_execution]
      @execution_id = @EXECUTION_ID
 
END 

There are a number of values that we need in order to execute an SSIS package from the catalog. I created the ProcedureToCreateExecuteMapping table to store these values. The ProcedureName column allows me to retrieve the parameter values based on the stored procedure that is running.

In order to execute the SSIS package from the catalog, we have to execute at least two stored procedures from the SSISDB database: create_execution and start_execution. In create_execution we specify the details need to identify the package to be executed (folder name, project name and package name). We also specify the reference id for the environment. When create_execution is successful, it returns an EXECUTION_ID. The SSIS package is not running yet.

After create_execution we may execute other stored procedures. In this case we execute set_execution_parameter_value, which allows us to specify the value for the Customer_Flat_File_Name package parameter. We pass in a file name to load and the EXECUTION_ID value returned from create_execution.

Finally, we execute the start_execution stored procedure, passing in the EXECUTION_ID value returned from create_execution. At this point the SSIS package is running.

Before we continue on to execute the stored procedure, I want to clarify one detail. The ProcedureToCreateExecutionMapping table returned a [REFERENCE_ID] column, which is the environment reference, connecting the project to the environment. In order to get this value, you have to perform the following queries:

-- get the folder_id
SELECT *
FROM [SSISDB].[catalog].[folders]
WHERE [name] = 'CRM'
-- [folder_id] = 20004
 
-- get the project_id
SELECT *
FROM [SSISDB].[catalog].[projects]
WHERE [folder_id] = 20004
-- [project_id] = 40007
 
-- get the environment reference id
SELECT *
FROM [SSISDB].[catalog].[environment_references]
WHERE [project_id] = 40007
-- [reference_id] = 30005 

In order to get the environment reference, you have to get the folder_id and project_id using the views in the SSISDB database as shown above.

Execute the Stored Procedure

Now that we have created the stored procedure to execute an SSIS package from the catalog, it’s time to run it. Use the following T-SQL to run the stored procedure:

DECLARE @P_EXECUTION_IDBIGINT
 
EXEC [dbo].[ExecuteLoadCustomersSSISPackage]
    @CUSTOMER_FLAT_FILE_NAME=N'CustomerSample.csv'
,   @EXECUTION_ID= @P_EXECUTION_IDOUTPUT
 
SELECT @P_EXECUTION_ID
 
SELECT
    [start_time]
,   [end_time]
,   [status]
FROM [SSISDB].[catalog].[executions]
WHERE [execution_id]= @P_EXECUTION_ID

The SSISDB.catalog.create_execution stored procedure returned the @execution_id OUTPUT parameter. The above stored procedure returns it as an OUTPUT parameter as well. To check on the status of the SSIS package, check the [SSISDB].[catalog].[executions] view. The [status] column has several possible values with success = 7. Check catalog.executions to get the details. You may have to query catalog.executions multiple times before the SSIS package has either failed or succeeded.

The SSIS catalog provides details on SSIS package executions. Return to the Integration Services Catalogs in the SSMS Object Explorer, right-click the SSIS package, select Reports, Standard Reports, and All Executions as shown below:

The execution details will be displayed as shown below:

In the above case you can see that the execution succeeded. There are multiple links available to retrieve additional details. I will cover the key points on the Overview link: Execution Overview and Parameters Used.

The Execution Overview shows the tasks in the SSIS package that have been run and a link in the Execution Path column to provide more information:

The Parameters Used shows the actual values of the project and package parameters, as well as some other information:

Being able to see the actual values used is extremely useful when you are troubleshooting.

Next Steps

• Executing SSIS packages from the Integration Services Catalog using a stored procedure opens up all sorts of interesting possibilities that you can use in your applications.
• In addition to taking advantage of existing SSIS packages, you can build new ones to perform the data movement operations that you need.
• Take a look at the sample code and test in your environment.
• There are many tips available in the Integration Services Category. Take a look for other interesting ones.