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

,@PackageName nvarchar(260)
,@ExecutionID bigint Output
,@LoggingLevel varchar(16) = 'Basic'
,@Use32BitRunTime bit = 0
,@ReferenceID bigint = NULL
,@ObjectType smallint = 50
As
begin
Set NoCount ON
/* Call the catalog.create_execution stored procedure
to initialize execution location and parameters */
Exec catalog.create_execution
@package_name = @PackageName
,@execution_id = @ExecutionID Output
,@folder_name = @FolderName
,@project_name = @ProjectName
,@use32bitruntime = @Use32BitRunTime
,@reference_id = @ReferenceID
/* Populate the @ExecutionID parameter for OUTPUT */
Select @ExecutionID As Execution_Id
/* 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