Execute SSIS Package from Stored Procedure in SQL Server

By:   |   Comments (19)   |   Related: More > Integration Services Execute Package Options


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?


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]
   -- 1. declare variables
   ,   @FOLDER_NAME     NVARCHAR(128)
   -- 2. get this stored procedure's name
   -- 3. get the parameters for executing the SSIS package
   FROM [dbo].[ProcedureToCreateExecutionMapping]
   WHERE [ProcedureName] = @PROCEDURE_NAME;
   -- 4. check if 1 row was returned
   IF @@ROWCOUNT <> 1
      -- throw error
   -- 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;

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
FROM [SSISDB].[catalog].[folders]
WHERE [name] = 'CRM';
-- [folder_id] = 20004
-- get the project_id
FROM [SSISDB].[catalog].[projects]
WHERE [folder_id] = 20004;
-- [project_id] = 40007
-- get the environment reference id
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:

EXEC [dbo].[ExecuteLoadCustomersSSISPackage]
,   [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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, October 3, 2023 - 8:46:07 AM - Raymond Barley Back To Top (91619)
Randall - execute the code in your post directly in SQL Server Management Studio using the appropriate values for the parameters. First execute [SSISDB].[catalog].[create_execution] and check the results. Then execute execute [SSISDB].[catalog].[start_execution] @execution_id. Doing it this way might shed some light on what's happening.

Monday, October 2, 2023 - 3:54:53 PM - Randall Powell Back To Top (91616)

DECLARE @execution_id bigint, @output_execution_id bigint
EXECUTE [SSISDB].[catalog].[create_execution]
@folder_name = 'my_folder'
, @project_name = 'my_project'
, @package_name = 'my_package'
, @execution_id = @execution_id output
EXECUTE [SSISDB].[catalog].[start_execution] @execution_id
SET @output_execution_id = @execution_id

Throws the following error in SSMS:

Msg 27184, Level 16, State 1, Procedure internal.prepare_execution, Line 115 [Batch Start Line 3]
In order to execute this package, you need to specify values for the required parameters.

What additional parameters are required? When I looked at the parameter list for [internal].[prepare].[execution] the only required parameter shows to be @execution_id. I don't understand why I am receiving this message.

Friday, December 9, 2022 - 9:58:28 AM - RAYMOND BARLEY Back To Top (90753)
Responding to Wednesday, December 7, 2022 - 5:57:17 PM - Mike:

In order to accomodate multiple parameters, change the stored procedure so that it retrieves the parameter values from a know location; e.g. a common table that holds parameter values. I took a shortcut but just hard-wiring to 1 parameter as my intent was to demonstrate the idea of executing an SSIS packake from a stored procedure.

Wednesday, December 7, 2022 - 5:57:17 PM - Mike Back To Top (90752)
How does the code need to be if there are 2 parameters?
Add a new line in 1 or run it adding another parameter value?

Thursday, January 27, 2022 - 1:36:50 PM - RAYMOND BARLEY Back To Top (89704)
There is no requirement that you use environment variables. The benefit of environment variables is that you can change the values used in the package without ever changing the package itself. For instance if you have DEV, TEST and PROD environments, the values used for the environment variables would be different in each. You don't want to have to open up SSIS packages to change "configuration" values.

Thursday, January 27, 2022 - 11:04:32 AM - Brick Back To Top (89703)
Why we need environment variables? Is it possible to exclude them? I have parameters on Package level which are enough I think. Also What If we hard code the FOLDER_NAME, PACKAGE_NAME, etc.? Is it necessary to have a table like yours?

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.

get free sql tips
agree to terms