The sample package outlined in this post can be downloaded here.
While my last post discussed SSIS object variables, this post will look at variable value assignment. There are several ways that a variable can be assigned a value within an SSIS package:
Default Value Assignment
Once a variable is declared, scoped, and a data type defined, a default value can be assigned within the Value field. This is the value that the variable will take once it falls in scope. This will remain the variable value until a task changes that value:
Expression
An SSIS expression can be used to assign a value to a variable which, like the Value property, is the value that will be assigned once the variable falls into scope. In SSIS 2005-2008R2, the variable expression value had a limitation to 4,000 characters. This could be overcome by using an expression to concatenate several together. I outlined this technique here.
Expression Task
An expression task, first introduced in SSIS 2012, provides the ability to use an SSIS expression to assign a value to a variable once the task is executed:
Script Task
A script task provides two separate means for variable value assignment.
ReadOnly/ReadWrite
The first, and probably the easiest, is to list the variables for ReadOnly or ReadWrite access within the components configuration pane:
With the variable(s) being entered within the component code is written that we don’t see that handles the locking and unlocking of the variable(s) for both read and write. The system generated code allows us to interact directly with the variables without having to first lock the variables. For example, the below VB.NET code will use the MessageBox Show method to display the value of the variable “Today” that was enabled for ReadWrite access, then change the value, and once again display the new value in a message box:
MessageBox.Show(Dts.Variables(0).Value.ToString)
Dts.Variables(0).Value = Now.AddDays(-1)
MessageBox.Show("The new date is " + Dts.Variables(0).Value.ToString)
This provides quick and easy access to the variable for read and write, but does limit when the variable is locked and unlocked based on the system generated code. For more granular control over variable locking, you can utilize the VariableDispenser within your code.
VariableDispenser
Utilizing the VariableDispenser method does require more code, but again provides complete control over the locking and unlocking of variables. DO NOT include the variable within the script configuration ReadOnly or ReadWrite property when using this method or an error will arise when you attempt to programmatically lock or unlock the variables since the components system generated code handles this. The below VB.NET code demonstrates using the VariableDispenser to work with variable directly within a script task:
Public Sub Main()
Dim vars As Variables = Nothing
Dim myVar As Variable
Dts.VariableDispenser.LockForRead("User::Today")
Dts.VariableDispenser.GetVariables(vars)
For Each myVar In vars
MessageBox.Show("Variable value " + myVar.Value.ToString + " and name is " + myVar.Name)
Next
Dts.VariableDispenser.LockForWrite("User::Today")
For Each myVar In vars
myVar.Value = Now
Next
For Each myVar In vars
MessageBox.Show("Variable value " + myVar.Value.ToString + " and name is " + myVar.Name)
Next
vars.Unlock()
Dts.TaskResult = ScriptResults.Success
End Sub
The above code uses a foreach loop to iterate through all locked variables, which in this case is only the User::Today variable. To access the variable properties and methods directly, the vars(i) can be called, where “i” is the zero based indexed value of the variable. For example, to re-assign the today variables value, the following code could be used to assign the current:
vars(0).Value = Now
Execute SQL Task
The execute SQL task can also be used to assign a variable a value in two different ways.
Output Parameter
Variable assignment can be done by using a query that assigns the result(s) to a placeholder of a “?“. For example, the below query would take the result of SELECT GETDATE() and assign it to the output parameter that is being held with the “?”
SELECT ? = GETDATE()
Using the query with the placeholder alone does not complete the assignment and still requires the mapping of the variable to the output parameter which is done on the Parameter Mapping page. On the parameter mapping, you select the variable, in this case User::Today, the direction, which is output, and the ParameterName, which will be the zero based index of the value, in this case 0. The question often comes up as to whether a named output parameter can be used rather than the indexed value and the truth is that it depends upon the connection manager. This post outlines the different configuration methods available for output parameters.
Result Set
Another way to use an execute SQL task to assign a value to a variable is by using a result set. This is very similar to an output parameter and only differs in how the query is written, there is no “?” place holder or parameter name, but rather just the result set type and result set mapping. On the execute SQL configuration page result set type must be defined as None, Single row, Full result set, or XML, the type is completely dependant upon the type of result(s) that will be provided from the query:
In the Result Set pane, you again map the zero based index results to the return value(s) of the query. In this case, there is only one query that will return a result so the indexed value is zero.
One benefit that you may immediately notice is that if you have multiple variables requiring assignment, such as the current date and the last date a process ran, you can do this within one single execute SLQ task using output parameters, while it would require 2 execute SQL tasks each with a single row result set. There is the possibility to utilize one execute SQL task using a result set of a full result set or possibly XML this would be overkill in such a situation and more easily accomplished with output parameters.
Data Flow Task
The data flow task can be used for variable assignment, for example, using the Row Count transformation to assign the number of rows that pass between two data flow components, or a complete result set using a Recordset destination. To go back to my previous post that covered the object variable data type, I used an execute SQL task to populate the variable. Rather than an execute SQL task, I can use a data flow task that gets the FirstName and LastName columns from the Adventureworks2012.Person.Person table and sends the result set to a Recordset destination mapping my object variable, in this case the User::Names variable:
The sample package outlined in this post can be downloaded here.
