Execution Patterns - SQL Server Integration Services Design Patterns - page 43

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution]
@package_name=N'Chapter2.dtsx'
,@execution_id=@execution_id OUTPUT
,@folder_name=N'Chapter2'
,@project_name=N'Chapter2'
,@use32bitruntime=False
,@reference_id=Null
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@execution_id
,@object_type=50
,@parameter_name=N'LOGGING_LEVEL'
,@parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO
You can use these same stored procedures to execute SSIS packages in the SSIS
Catalog! In fact, I designed a script to create a wrapper-stored procedure that will call
the T-SQL statements executed when an SSIS package is executed in the SSIS Catalog.
You can see that script in Listing 2-2 .
Listing 2-2 . Script to Build a Wrapper-Stored Procedure for Executing SSIS Packages
in the SSIS Catalog
/* Select the SSISDB database */
Use SSISDB
Go
/* Create a parameter (variable) named @Sql */
Declare @Sql varchar(2000)
/* Create the Custom schema if it does not already exist
*/
print 'Custom Schema'
If Not Exists(Select name
From sys.schemas