Fun With The ExecValueVariable Property

              The sample package displayed in this post can be downloaded here.  In addition to the tasks noted there are several other tasks that are used to demonstrate the ExecValueVariable property.

              While exploring options to ease custom logging and reporting I came across an interesting, confusing, and poorly documented property that is available in several control flow tasks.  I was hoping to find a means to simplify capturing rows that were passed through a data flow without using a row count transformation of rows affected within an execute SQL task without using a result set or output parameter when I found the ExecValueVariable property.  I was pretty fired up and thought that I had discovered the silver bullet that would meet all my custom logging needs.  As I began to examine this property I found that it accepted a user variable and immediately thought that it would capture the affected rows or actions of the task. 

               I configured a data flow task’s ExecValueVariable property with a variable, called ExecValueVariable, of a data type integer and fired it off believing that the end result would be variable containing the number of affected rows from the task.

ExecValueVariable 

              I was a bit dismayed to receive an error, “Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property”.  After this minor setback I began my internet research to find out what I had done wrong.  I came across a blog post here which gave me some more insight as to what this property does and does not do.  Basically the ExecutionValueVariable can be used to capture the value of rows or objects affected, but the task must have defined the ExecValue.  There are several tasks that implement the ExecValue and allow you to assign a variable to capture this value.

SSIS Control Flow Task

Value Captured

Execute SQL Number of rows affected
File System Number of files/Folders affected
Transfer Jobs Number of jobs moved
Transfer Error Messages Number of error messages moved
Transfer SQL Server Objects Number of objects moved

 

              Armed with this information I wanted to test the limits of this property and see exactly how it works so I began with an execute SQL task and a message box.  I used both DDL and DML statements in the execute SQL task to see how the ExecValueVariable would handle this:

CREATE TABLE SSISExec(
col1 INT);
GO

INSERT SSISExec
VALUES(1);
GO

DROP TABLE SSISExec;
GO
                                                  ExecValueVariable configurationXQTSQLVarProp 
                                                             Control FlowExecSQLValVariable
              Executing the task the result is what would be expected, a message box showing 1, the number of rows affected in the execute SQL task.  I edited the execute SQL task and added SET NOCOUNT ON in the top of the script and again executed the package.  The execution of the task succeeded, but the message box returned with a value of -1.  This demonstrates that the ExecValue is using the @@ROWCOUNT to assign the value of the variable and absent a @@ROWCOUNT the value returned is -1.

                 I was feeling pretty good about myself now so decided to experiment a bit with the ExecValueVariable again with a data flow task.  If you look at the chart above that outlines the tasks that support this property you will obviously notice that the data flow task is not mentioned, but IT STILL has this property.  I figured it was there for a reason right?!?!

              In my data flow task I used a source to the AdventureWorks2012 database with a simple query:

SELECT FirstName,
MiddleName,
LastName
FROM Person.Person

I then used a flat file destination for the query results and configured the ExecVariableValue once again. I directed the on failure precedence constraint to another script task to display the variable value in a message box.

 

                                                      Control flow task CtrlFlowExecVariable
                                            Data flow task with source and destinationDataFlowName

              The results are exactly what I expected.  The data flow task failed and the message box displayed the default value assigned to the variable in the ExecValueVariable.  The reason for the failure is made apparent in the error message I shared above, “Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property”.  The component must use the ExecValue method to assign a value to the placeholder variable in the ExecValueVariable.

              Unfortunately there is no way to configure this within a dataflow task, but using a script task we can demonstrate implementing the ExecValue.  I added two script tasks in my package and configured the on success precedence constraint connecting the two.  The first script is used to set the ExecValue property and use a message box to display the user defined variable ExececutionVariableValue of the task.  The ExecValueVariable property in the first task configured to use the ExececutionVariableValue variable:

Dts.ExecutionValue = 9999;

MessageBox.Show(“Assignment of ExecVariableValue occurs AFTER task completion. The current value is : “ + Dts.Variables[“ExecValueVariable”].Value.ToString());
 
 The second script task displays a message box that displays the new assigned value.

MessageBox.Show(“Assignment Complete: “ + Dts.Variables[“ExecValueVariable”].Value.ToString());

 Executing the package displays the message boxes:

execvarbox1
 execvarbox2

Leave a Reply


Hit Counter provided by Sign Holders