Executing SSIS: Run SSIS Packages in the SSIS Catalog via Stored Procedure

In my article titled A Custom Execution Method – Level 19 of the Stairway to Integration Services posted at SQL Server Central, I created a stored procedure to execute SSIS packages stored in the SSIS Catalog. Really, all I did was build a rudimentary wrapper for the stored procedures used by the SSIS Catalog to execute packages. To learn more about what I did, and why and how I did it, please read the article. It’s good. I promise. Would I lie?

One benefit: the SSIS Catalog does not (easily) facilitate code re-use. If you want to execute the same package in multiple SSIS Catalog projects, the Execute Package Task is going to force you to import those packages into your existing project. What happens if you update this package later? You have to update every copy of the package… and then redeploy – and test – each project. Or, you can use an Execute SQL Task to call this stored procedure, and execute any SSIS package. In any project. In any folder.

The Transact-SQL script that follows adds a stored procedure to the “custom” schema (please create the custom schema first) to the SSISDB database (the database used by the SSIS Catalog). It creates an intent-to-execute, configures the intent-to-execute, and then executes the SSIS package.

Use SSISDB
go

print ‘Custom.execute_catalog_package stored procedure’

If Exists(Select s.name + ‘.’ + p.name
          From   sys.procedures p
                 Join sys.schemas s
                   On s.schema_id = p.schema_id
          Where  s.name = ‘custom’
                 And p.name = ‘execute_catalog_package’)
  begin
      print ‘ – Dropping custom.execute_catalog_package’ 
      Drop Procedure custom.execute_catalog_package 
      print ‘ – Custom.execute_catalog_package dropped’
  end

print ‘ – Creating custom.execute_catalog_package’
go

Create Procedure custom.execute_catalog_package
  @package_name    nvarchar(260),
  @folder_name     nvarchar(128),
  @project_name    nvarchar(128),
  @use32bitruntime bit = false,
  @reference_id    bigint = NULL,
  @logging_level   varchar(11) = ‘Basic’
As
  begin
      — create an Intent-to-Execute
      declare @execution_id bigint

      exec [SSISDB].[catalog].[create_execution]
        @package_name=@package_name,
        @execution_id=@execution_id output,
        @folder_name=@folder_name,
        @project_name=@project_name,
        @use32bitruntime=@use32bitruntime,
        @reference_id=@reference_id

      — Decode and configure the Logging Level
      declare @var0 smallint = Case
          When Upper(@logging_level) = ‘NONE’ Then 0
          When Upper(@logging_level) = ‘PERFORMANCE’ Then 2
          When Upper(@logging_level) = ‘VERBOSE’ Then 3
          Else 1 — Basic
        End

      exec [SSISDB].[catalog].[set_execution_parameter_value]
        @execution_id,
        @object_type=50,
        @parameter_name=N’LOGGING_LEVEL’,
        @parameter_value=@var0

      — Start the execution
      exec [SSISDB].[catalog].[start_execution]
        @execution_id

      — Return the execution_id
      Select @execution_id As execution_id
  end

go

print ‘ – Custom.execute_catalog_package created.’
go 

Use this handy stored procedure to execute SSIS packages via T-SQL, regardless of which SSIS Catalog folder and project contain the packages.

Learn more:
Advanced SSIS Training with Tim Mitchell and Andy Leonard in Dallas, TX 9-11 Mar 2015
Watch the Video
Get the Script
Linchpin People Blog: SSIS
Stairway to Integration Services

:{>