Go ExecValueVariable of SSIS

The ExecutionValue execution Value property and it s friend ExecValueVariable is a much undervalued feature of SSIS, and Many people I talk to is not even aware of their existence, so I thought I ' d try and raise their profile a bit.

The ExecutionValue property was defined on the base object Task and so all tasks had it available, but it was up to the TAS K developer to does something useful with it. The basic idea behind it's it allows the task to return something useful and interesting about what it had performed , in addition to the standard success or failure result. The best example perhaps are the Execute SQL Task which uses the ExecutionValue property to return the number of rows AFFEC Ted by the SQL statement (s). This was a very useful feature, something people often want to capture into a variable, and start using the result set Opti ONS to do. Unfortunately we cannot read the value of a task in runtime from within a SSIS package, so the ExecutionValue pro Perty on it own is a bit of a let down, but enter the execvaluevariable and we have the perfect marriage.

The execvaluevariable is another property exposed through the task (TaskHost), which lets us select a SSIS package variabl E. What happens are then when the task sets the ExecutionValue, the interesting value was copied into the variable we SE T on the ExecValueVariable property, and a variable are something we can access and do something with. So put simply if the ExecutionValue property value was of interest, make sure you create yourself a package variable and SE t the name as the execvaluevariable. The look for the 3 step guide below:

1 Configure your task as normal, for example the Execute SQL task, which here calls a stored procedure to do some updates.

2 Create variable of a suitable type to match the ExecutionValue, an integer was used to match the result we want to capture, The number of rows.

3 Set the execvaluevariable for the task, just select the variable we created in step 2. You need to does this in Properties grid for the task (Short-cut key, select the task and press F4)

Now if we execute the sample task above, our variable updatequeuerowcount would get the number of rows we updated in our Execute SQL Task.

I ' ve tried to collate a list of tasks that return something useful via the executionvalue and execvaluevariable mechanism, But the documentation isn ' t always great.

Task	ExecutionValue Description
Execute SQL Task	Returns the number of rows affected by the SQL statement or statements.
File System Task	Returns the number of successful operations performed by the task.
File Watcher Task	Returns the full path of the file found
Transfer Error Messages Task	Returns the number of error messages that has been transferred
Transfer Jobs Task	Returns the number of jobs that is transferred
Transfer Logins Task	Returns the number of logins transferred
Transfer Master Stored Procedures Task	Returns the number of stored procedures transferred
Transfer SQL Server Objects Task	Returns the number of objects transferred
WMI Data Reader Task	Returns An object that contains the results of the task. Not exactly clear, but I assume it depends on the WMI query used.

[ExecValueVariable of]ssis