Execute Sql Task的ExecValueVariable 用法

The TaskHost exposes the ExecutionValue property through the ExecValueVariable property. The task uses the ExecutionValue property to provide optional, supplemental information about the results of execution. The ExecValueVariable property allows the user to map the ExecutionValue that the task returns to any variable that is visible to the task. The package could then use the value that is returned as one of the criteria for determining the next task to run in the control flow. For example, if a task deletes rows from a table as part of its Execute method, the task might return the number of rows deleted as the ExecutionValue. Clients of the task could then use this value to define expressions in precedence constraints between tasks.

The ExecutionValue property can be defined on the object Task and all tasks have this property. The best example perhaps is the Execute SQL Task which uses the ExecutionValue property to return the number of rows affected by the SQL statement(s). This could be a useful feature which you may often want to capture into a variable and using the result to do something else. We cann‘t read the value of a task property at runtime from SSIS but we can use ExecValueVariable to get it.

The ExecValueVariable property exposed through the task which lets us select a package variable. When the task sets the ExecutionValue, the actual value is copied into the variable we set on the ExecValueVariable property and a variable is something we can access and do something with. So if you are interested in ExecutionValue property  then make sure you create a package variable and set the name as the ExecValueVariable.

在Execute SQL Task中，使用该属性指定一个变量，用以接收受影响的数据行数。Returns the number of rows affected by the SQL statement(s).The ExecValue is using the @@ROWCOUNT to assign the value of the variable and absent a @@ROWCOUNT the value returned is -1.

示例 Execute Sql Task的ExecValueVariable 用法

1，design package的ui

Execute Sql Task的sql语句是，属性ExecValueVariable的值是变量varCount

insert into dbo.delay_test
VALUES(1),(2),(3)

insert into dbo.delay_test
VALUES(2),(3)

Task insert Data的sql语句是，传入的参数是User::varCount

insert into dbo.dt_test
values(?)

2，查看结果，第一个Task返回的结果是varCount是2，这个结果实际上是@@RowCount，SSIS在执行语句之后，将@@RowCount赋值到Execute SQL Task 属性ExecValueVariable指定的变量中。

Execute Sql Task的ExecValueVariable 用法

标签：