SSISDB5：Parameter

二，通过TSQL 来使用参数

declare @Execution_ID bigint
exec catalog.create_execution @package_name=N‘Test.dtsx‘,
    @Execution_ID=@Execution_ID output,
    @folder_name=N‘MyProjectFloder‘,
    @project_name=N‘MyProject‘,
    @use32bitruntime=FALSE,
    @reference_id=null

declare @var0 sql_variant=N‘Package_Parameter_value‘;
exec catalog.set_execution_parameter_value 
    @Execution_ID=@Execution_ID,
    @object_type=30,        --Package
    @parameter_name=N‘Package_Parameter_Name‘,
    @parameter_value=@var0

exec catalog.start_execution @Execution_ID=@Execution_ID

三，特殊参数

SSISDB的特殊参数，用于控制Package执行时的特殊行为，这些参数的值，通过 catalog.set_execution_parameter_value 来设置。

• LOGGING_LEVEL

• CUSTOMIZED_LOGGING_LEVEL

• DUMP_ON_ERROR

• DUMP_ON_EVENT

• DUMP_EVENT_CODE

• CALLER_INFO

• SYNCHRONIZED

 LOGGING_LEVEL – Integer – 0 | 1* | 2 | 3 – Sets the logging level for this execution.  The values represent no logging, basic, performance, and verbose levels respectively.

SYNCHRONIZED - Boolean - 0 synchronous | 1 asynchronous

参考文档：《SSIS Parent-Child Architecture in Catalog Deployment Mode》

First of all, by default when executing a package using T-SQL, the package is started asynchronously.  This means that when you call the stored procedure [SSISDB].[catalog].[start_execution], the T-SQL command will return immediately (assuming you passed in a valid package name and parameters), giving no indication of either success or failure.  That’s why, on this example, I’m setting the execution parameter named SYNCHRONIZED to force the T-SQL command to wait until the package has completed execution before returning.  (Note: For additional information about execution parameters, check out this post by Phil Brammer).  Second, regardless of whether you set the SYNCHRONIZED parameter, the T-SQL command will not return an error even if the package fails. 

DECLARE @execution_id BIGINT

EXEC [SSISDB].[catalog].[create_execution] @package_name = N‘ChildPkgRemote.dtsx‘
    ,@execution_id = @execution_id OUTPUT
    ,@folder_name = N‘SSIS Parent-Child‘
    ,@project_name = N‘SSIS Parent-Child Catalog Deployment - Child‘
    ,@use32bitruntime = False
    ,@reference_id = NULL

-- Set user parameter value for filename
DECLARE @filename SQL_VARIANT = N‘E:\Dropbox\Presentations\_sampleData\USA_small1.txt‘

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id
    ,@object_type = 30
    ,@parameter_name = N‘pSourceFileName‘
    ,@parameter_value = @filename

-- Set execution parameter for logging level
DECLARE @loggingLevel SMALLINT = 1

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id
    ,@object_type = 50
    ,@parameter_name = N‘LOGGING_LEVEL‘
    ,@parameter_value = @loggingLevel

-- Set execution parameter for synchronized
DECLARE @synchronous SMALLINT = 1

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id
    ,@object_type = 50
    ,@parameter_name = N‘SYNCHRONIZED‘
    ,@parameter_value = @synchronous

-- Now execute the package
EXEC [SSISDB].[catalog].[start_execution] @execution_id

-- Show status
SELECT [status] AS [execution_status]
FROM SSISDB.CATALOG.executions
WHERE execution_id = @execution_id

参考文档：

catalog.object_parameters (SSISDB Database)

catalog.set_execution_parameter_value (SSISDB Database)

Quick Tip – Run SSIS 2012 packages synchronously and other execution parameters

SSISDB5：Parameter

标签：