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

Else 0 /* 'None' */
End
/* Call the catalog.set_execution_parameter_value
stored

procedure to update the LOGGING_LEVEL parameter */
Exec catalog.set_execution_parameter_value
@ExecutionID
,@object_type = @ObjectType
,@parameter_name = N'LOGGING_LEVEL'
,@parameter_value = @logging_level

/* Call the catalog.start_execution (self-explanatory)

*/

Exec catalog.start_execution
@ExecutionID

end

GO

If you execute this script to create the custom schema and stored procedure in your
instance of the SSISDB database, you can test it using the statement in Listing 2-3 .

Listing 2-3 . Testing the SSISDB.custom.execute_catalog_package Stored Procedure

Declare @ExecId bigint
Exec SSISDB.custom.execute_catalog_package
'Chapter2','Chapter2','Chapter2.dtsx',
@ExecId Output

Adding a Data Tap

The SSISDB.custom.execute_catalog_package stored procedure can be
modified slightly to create a data tap — a new feature for packages executed from the
SSISDB Catalog in SSIS 2014. Adding a few parameters and some T-SQL to the
stored procedure allows it to execute an SSIS package and export a comma-separated
values (CSV) file filled with some or all of the rows that flowed through a point in a
Data Flow task. Data taps provide a much-needed window on the state of data as they