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

/* Populate the @ExecutionID parameter for OUTPUT */
Select @ExecutionID As Execution_Id
/* Configure Data Tap parameters */
If (Left(@DataFlowTaskName, 9) <> '\Package\')
Set @DataFlowTaskName = '\Package\' + @DataFlowTaskName
If Left(@IdentificationString,6) <> 'Paths['
Set @IdentificationString = 'Paths['
+ @IdentificationString + ']'
/* Create the Data Tap */
EXEC [SSISDB].[catalog].add_data_tap @ExecutionID,
@DataFlowTaskName,
@IdentificationString, @DataTapFileName,
@DataTapMaxRows
/* Create a parameter (variable) named @Sql */
Declare @logging_level smallint
/* Decode the Logging Level */
Select @logging_level = Case
When Upper(@LoggingLevel)
= 'BASIC'
Then 1
When
Upper(@LoggingLevel) = 'PERFORMANCE'
Then 2
When
Upper(@LoggingLevel) = 'VERBOSE'
Then 3
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