Using Variables in the Script Task
Variables make it possible for the Script task to exchange data with other objects in the package. For more information, see Integration Services Variables.
|The Value property of the Variable class is of type Object. Because the Script task has Option Strict enabled, you must cast the Value property to the appropriate type before you can use it.|
You add existing variables to the ReadOnlyVariables and ReadWriteVariables lists in the Script Task Editor to make them available to the custom script. Keep in mind that variable names are case-sensitive. Within the script, you access variables of both types through the Variables property of the Dts object. Use the Value property to read from and write to individual variables. The Script task transparently manages locking as the script reads and modifies the values of variables.
You can also use the VariableDispenser property (Dts.VariableDispenser) to work with variables in the Script task. When using the VariableDispenser, you must handle both the locking semantics and the casting of data types for variable values in your own code. You may need to use the VariableDispenser property instead of the Variables property if you want to work with a variable that is not available at design time but is created programmatically at run time.
When a Script task runs repeatedly within a Foreach Loop container, the script usually needs to work with the contents of the current item in the enumerator. For example, when using a Foreach File enumerator, the script needs to know the current file name; when using a Foreach ADO enumerator, the script needs to know the contents of the columns in the current row of data.
Variables make this communication between the Foreach Loop container and the Script task possible. On the Variable Mappings page of the Foreach Loop Editor, assign variables to each item of data that is returned by a single enumerated item. For example, a Foreach File enumerator returns only a file name at Index 0 and therefore requires only one variable mapping, whereas an enumerator that returns several columns of data in each row requires you to map a different variable to each column that you want to use in the Script task.
After you have mapped enumerated items to variables, then you must add the mapped variables to the ReadOnlyVariables property on the Script page of the Script Task Editor to make them available to your script. For an example of a Script task within a Foreach Loop container that processes the image files in a folder, see Working with Images with the Script Task.
The following example demonstrates how to access and use variables in a Script task to determine the path of package workflow. The sample assumes that you have created integer variables named
MaxRecordCount and added them to the ReadOnlyVariables collection in the Script Task Editor. The
CustomerCount variable contains the number of customer records to be imported. If its value is greater than the value of
MaxRecordCount, the Script task reports failure. When a failure occurs because the
MaxRecordCount threshold has been exceeded, the error path of the workflow can implement any required clean-up.
Public Sub Main() Dim customerCount As Integer Dim maxRecordCount As Integer If Dts.Variables.Contains("CustomerCount") = True AndAlso _ Dts.Variables.Contains("MaxRecordCount") = True Then customerCount = _ CType(Dts.Variables("CustomerCount").Value, Integer) maxRecordCount = _ CType(Dts.Variables("MaxRecordCount").Value, Integer) End If If customerCount > maxRecordCount Then Dts.TaskResult = Dts.Results.Failure Else Dts.TaskResult = Dts.Results.Success End If End Sub