Execute an SSIS Package Through SQL Stored Procedure

Introduction

I want to briefly describe how to execute your SSIS Package from a SQL Strored Procedure, which in-turn can be executed by a C# application passing in any application variables the package may use.

Background

Sometimes, you will want to execute your SSIS Package in a manual fashion, not going into SQL Server Management Studio to right click on the package and execute it. But, creating a C# console that you can simply click on your desktop to start the SSIS Package in question.

This can be achieved very simply using a strored procedure to kick of the package, and your C# code will execute the stored procedure (passing any parameters you wish to bind from a prompt on your C# console application or looking up an XML file for example.)

Deploy Your SSIS Package

Once you have created your SSIS Package, right click your solution and select Deploy from the context menu.

Enter your SQL Server details.

Create Stored Procedure

Create a simple stored procedure, to execute the following SSIS Package - in the image below, you can see the mapping used.

declare @intExceptionID bigint;
exec catalog.create_execution 'DRM', 'DRM_SSIS', _
'DRM Import Process.dtsx', NULL, 0, @intExceptionID out
exec [SSISDB].catalog.start_execution @intExceptionID
GO

Below, you can see how the parameters are mapped to the execution statement.

Create_Execution Explained

create_execution [ @folder_name = folder_name
     , [ @project_name = ] project_name
     , [ @package_name = ] package_name
  [  , [ @reference_id = ] reference_id ]
  [  , [ @use32bitruntime = ] use32bitruntime ]
     , [ @execution_id = ] execution_id OUTPUT

[ @folder_name = ] folder_name

The name of the folder that contains the package that is to be executed. The folder_name is nvarchar(128).

[ @project_name = ] project_name

The name of the project that contains the package that is to be executed. The project_name is nvarchar(128).

[ @package_name = ] package_name

The name of the package that is to be executed. The package_name is nvarchar(260).

[ @reference_id = ] reference_id

A unique identifier for an environment reference. This parameter is optional. The reference_id is bigint.

[ @use32bitruntime = ] use32bitruntime

Indicates if the 32-bit runtime should be used to run the package on a 64-bit operating system. Use the value of 1 to execute the package with the 32-bit runtime when running on a 64-bit operating system. Use the value of 0 to execute the package with the 64-bit runtime when running on a 64-bit operating system. This parameter is optional. The Use32bitruntime is bit.

[ @execution_id = ] execution_id

Returns the unique identifier for an instance of execution. The execution_id is bigint.

Execute SQL Procedure Through C#

There is no magic here, I simply execute the SQL Sproc as normal.

static void Main(string[] args)
        {            
            using (var conn = new SqlConnection
            (System.Configuration.ConfigurationManager.AppSettings["ConnectionInfo"].ToString()))
            {
                using (var cmd = new SqlCommand("[dbo].[usp_ExecDrmPackage]", conn))
                {
                    conn.Open();                                        
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
            }
            Console.WriteLine("Your SSIS Package has been executed asynchronously. 
            \r\nYou will receive emails notifying you of it's status.
	    \r\nPress any key to close this console.");
            Console.ReadLine();
        }