Have you used the ExecutionValue and ExecValueVariable properties?

by Darren Green 4 May 2010 08:36

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

The ExecutionValue property is defined on the base object Task, so all tasks have it available, but it is up to the task developer to do something useful with it. The basic idea behind it is that it allows the task to return something useful and interesting about what it has performed, in addition to the standard success or failure result. 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 is a very useful feature, something people often want to capture into a variable, and start using the result set options to do. Unfortunately we cannot read the value of a task property at runtime from within a SSIS package, so the ExecutionValue property on its 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 variable. What happens now is that when the task sets the ExecutionValue, the interesting 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 put simply if the ExecutionValue property value is of interest, make sure you create yourself a package variable and set the name as the ExecValueVariable. Have  look at 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.

Execute SQL Task Editor

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

Adding a package variable

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

Setting the ExecValueVariable property

Now when we execute the sample task above, our variable UpdateQueueRowCount will 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 have been transferred
Transfer Jobs Task Returns the number of jobs that are 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.

Comments (1) -

10/14/2010 10:29:05 PM #

yowzermiller

Awesome little article - exactly what I needed!  I used it to identify when there are no records to process.  Then I used the variable in the following precedence constraint so that the rest of the package only runs if the variable is greater than zero.

yowzermiller United States

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Widget Amazon Book not found.

Object reference not set to an instance of an object.X

RecentComments

Comment RSS