Execute SSIS Package from Stored Procedure in SQL Server


By:   |   Updated: 2020-08-19   |   Comments (13)   |   Related: More > Integration Services Execute Package Options


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:

project parameters

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

 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:

package parameters

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:

Integration Service Catalogs

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:

ssis variables

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):

configure connection

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:

configure connection

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:

Integration Services Catalogs

The execution details will be displayed as shown below:

package executions

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:

package executions

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

parameters used

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.


Last Updated: 2020-08-19


get scripts

next tip button



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

View all my tips



Comments For This Article




Wednesday, December 30, 2020 - 8:04:48 AM - Ray Barley Back To Top (87979)
You can execute a SQL Agent job step using the Run As and specify a Proxy that has the permissions required. The Proxy uses a Credential and you specify an account for the Credential. You may still wind up giving the account elevated privileges but at least you're not giving elevated privileges to the SQL Agent service account.

Tuesday, December 29, 2020 - 1:15:56 PM - LukeL Back To Top (87970)
Ray, thanks for the concise writeup. We've been using similar logic for a number of years. Now we want to have the sql agent call the sproc from a job not owned by an SA and We're running into some security hurdles. The only way we seem to get this to work is to have the agent job run as a sys admin. do you have any other thoughts as to how we could perform this task from an agent job not running as an SA?

Wednesday, November 25, 2020 - 4:59:25 PM - RAYMOND BARLEY Back To Top (87851)
Take a look at the table SSISDB.internal.operation_messages to get the details on error messages logged from SSIS packages running from the SSIS Catalog. Check for message_type = 120. The operation_id is the execution_id you get from [SSISDB].[catalog].[create_execution].

Wednesday, November 25, 2020 - 10:58:14 AM - Tom Back To Top (87846)
I was able to get all of this working, so thank you for the detailed guide. Couldn't find anything like it anywhere else.If I put these stored procedures in an SSIS job, they'll all show as succeeding when run, but it's possible that the package could fail after execution. I was wondering how you would handle this if the package errors out after execution?

Tuesday, November 24, 2020 - 12:46:46 PM - Tom Back To Top (87837)
Perfect. Just what I was looking for. Thanks Raymond!

Tuesday, November 24, 2020 - 12:34:38 PM - RAYMOND BARLEY Back To Top (87836)
If you want to override the value assigned to a project parameter from the environment, I think you can execute the stored procedure [SSISDB].[catalog].[set_execution_parameter_value] and provide a different value.

Tuesday, November 24, 2020 - 11:31:38 AM - RAYMOND BARLEY Back To Top (87834)
You setup a project parameter in a package and after you deploy the package, you configure the project parameter to get its value from an environment variable at runtime. You run the package and the project parameter has the value from the environment variable.

I think of the project parameter as a default value. I may use the project parameter value or I may use some different value based on some business logic.

Tuesday, November 24, 2020 - 9:03:19 AM - Tom Back To Top (87833)
Got it, thank you! One more question. If I were to reference the environment for a package but in the stored procedure define different values for those same parameters, I assume the defined parameters take priority over the environment?

Tuesday, November 24, 2020 - 7:31:36 AM - RAYMOND BARLEY Back To Top (87830)
There is no requirement that you use project or package parameters. I use them so I can change values without changing the package itself. There is no requirement that you use environments either. I find the environment is convenient for setting values in all packages in a project.

Saturday, November 21, 2020 - 11:34:08 PM - Tom Back To Top (87820)
Hey, is using package parameters required for this? We have a solution that only uses project parameters. What would be different if this was the case? No environment? Thanks!

Wednesday, August 19, 2020 - 11:37:59 AM - Ray Barley Back To Top (86332)
Answers to some questions that have been asked:

Minimum SQL Server version is 2012 (that's when project deployment became available)

SSISDB Permissions or Roles required - see this link: https://docs.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-start-execution-ssisdb-database?view=sql-server-ver15 SInce you are creating your own stored procedures, you could take advantage of Execute As

How are multiple requests for the same package handled? Most of my experience with SSIS is executing packages demlyed to the catalog with SQL Agent. I have not found any issue in this scenario with requesting multiple packages to execute. For instance I have an SSIS package in a SQL Agent job. The package has 5 Execute Package tasks - they all execute the same package with different parameters. There are no precedence constraints. This job runs every 5 minutes and I haven't seen any issue with multiple requests to run the same package.

Wednesday, August 19, 2020 - 10:15:14 AM - Ian F Back To Top (86331)
Hello Ray, great tip I can definitely see myself referencing this in the future, thanks for the great tip!

Wednesday, August 19, 2020 - 10:09:10 AM - MNoreen Back To Top (86330)
We are in a similar situation where we have a lot of SSIS packages that we have developed over the years. Since we've used these packages in a .NET web-based application, we have always used the Microsoft.SqlServer.ManagedDTS library and c# code to load a package, set package parameters, and execute.

While this has worked well over the years, it continues to become more and more difficult with upgrades in the tangled mess of .NET, C#, Visual Studio and SQL Server. We keep looking for alternatives that would be "easier" to maintain over the long haul. This has possibilities, but I do have a couple of detail questions. For example:

What are the dependencies? Is there a minimum SQL Server version required? Any special SQL Server, SSISDB "permissions" or "roles" required to execute?

Does it work if multiple requests to execute the same package, with different params, at the same time? I'm not sure I've fully grokked where the boundaries are, where the contention might manifest... .net /ADO.NET code that sets up the connection and command objects to execute, or on the SQL Server instance runnning the proc...? Those are the kind of things I'm wondering about.

Thanks in advance.



download





Recommended Reading

Different ways to execute a SQL Server SSIS package

DTEXEC Command Line Parameters Using Command Files

How To Execute an Integration Services Package from a SQL Server Stored Procedure

How to Execute SSIS Packages from SQL Server Agent

Execute SSIS Package with SQLCMD and Parameters














get free sql tips
agree to terms