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

rows of data that flowed through the "FFSrc Temperature.Flat File
Source Output" data flow path in the "DFT Load Temperature Data" in
the DataTapTest.dtsx SSIS package.
Creating a Custom Execution Framework
SSIS execution frameworks support repeatable and reliable SSIS package execution.
The SSISDB.custom.execute_catalog_package stored procedure can be
used as the centerpiece for an SSIS execution framework. To create the tables to sup-
port this framework, execute the statements in Listing 2-7 .
Listing 2-7 . Tables to Support a Custom SSIS Execution Framework
/* Switch to SSISDB database */
Use SSISDB
Go
/* Build custom Schema */
print 'Custom Schema'
/* Check for existence of custom Schema */
If Not Exists(Select name
From sys.schemas
Where name = 'custom')
begin
/* Build and execute custom Schema SQL
if it does not exist */
print ' - Creating custom schema'
declare @CustomSchemaSql varchar(32) = 'Create Schema
custom'
exec(@CustomSchemaSql)
print ' - Custom schema created'
end
Else
/* If the custom schema exists, tell us */
print ' - Custom schema already exists.'
print ''
Go