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

move through an SSIS data flow, facilitating root-cause analysis and troubleshooting in
production environments without altering the package code. Data taps are one of the
most important enhancements to Integration Services 2014. Listing 2-4 contains the
script to build
SSISDB.custom.execute_catalog_package_with_data_tap :
Listing 2-4 . 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
Where name = 'custom')
begin
/* Create Schema statements must occur first in
a batch */
print ' - Creating custom schema'
Set @Sql = 'Create Schema custom'
Exec(@Sql)
print ' - Custom schema created'
end
Else
print ' - Custom Schema already exists.'
print ''
/* Drop the Custom.execute_catalog_package_with_data_tap
Stored Procedure if it already exists */
print 'Custom.execute_catalog_package_with_data_tap
Stored Procedure'