A lot of people still consistently ask me about how to read and write to variables in the SSIS script task. In this post I will demonstrate for you the two ways in which you can go about this, one from native SSIS functionality and the other from code. In SQL Server 2008 both will generally work out equally as well. In previous versions, you may want to stick with the coding piece as sometimes the a€˜automatica€™ integration with the variable dispenser was a little hooky.
So for our example, I am going to set up a simple SSIS project. First I create 2 variables: MyName & YourName. I have scoped these at the packages level as it has always been my instinct that unless there is a compelling reason to scope it otherwise.
Now that we have our variables set up, we are ready to begin making our project. Since this example is going to be pretty straightforward, we will just drop two script components onto the control flow design surface. The first will be the one using the native way of handling variables in the script component and the second will be using a little bit of custom code to do it programmatically. The setup is simply shown below.
Now opening up the script task you can click on the a€| button next to the readonlyvariables and readwritevariables areas. For this sample, I am setting up the MyName variable to be read only and the YourName variable to read-write.
When completed the screen will show our two variables in the boxes as shown below.
Now it is a matter of simply making a call, like below, to access your variables. For my example I am simply going to display the values in some message boxes. Reading from the MyName variable and overwriting the YourName variable. Pretty simple.
Public Sub Main()
Dim MyName As String = Dts.Variables("MyName").Value
MsgBox(MyName)
Dts.Variables("YourName").Value = "Tom"
Dim YourName As String = Dts.Variables("YourName").Value
MsgBox(YourName)
Dts.TaskResult = ScriptResults.Success
End Sub
The variables integration with the script tasks has gotten much better in SQL Server 2008 and I have yet to run into any problems like I have previously. Still, there may be some instances that you come across that you would rather programmatically access variables. For this the code below should do the trick. I have two functions: one for reading a variable and the other for writing to a variable. You will notice in both instances that you must lock the variable first before trying to access it. Think of it as SSISa€™s version of row locking. You want to make sure that you are getting the most accurate version of the variable at that point in time. What good would it do you,since things can run in parallel, if another task is updating the variable at the same time you are trying to read it?
Public Sub Main()
Dim MyName As String = ReadVariable("MyName")
MsgBox(MyName)
WriteVariable("YourName", "Tom")
Dim YourName As String = ReadVariable("YourName")
MsgBox(YourName)
Dts.TaskResult = ScriptResults.Success
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim rtnValue As Object
'Create a variables collection to hold you object
Dim var As Variables
Try
'Lock the variable first to make sure that you have exclusive access
'Think of it like a database object lock
Dts.VariableDispenser.LockOneForRead(varName, var)
'Now populate your result
rtnValue = var(varName).Value
Catch ex As Exception
Throw ex
Finally
'You must make sure that you unlock the variable before exiting routine
var.Unlock()
End Try
Return rtnValue
End Function
Private Sub WriteVariable(ByVal varName As String, ByVal value As Object)
'Create a variables collection to hold you object
Dim var As Variables
Try
'Lock the variable first to make sure that you have exclusive access
'Think of it like a database object lock
Dts.VariableDispenser.LockOneForWrite(varName, var)
'Now populate your result
var(varName).Value = value
Catch ex As Exception
Throw ex
Finally
'You must make sure that you unlock the variable before exiting routine
var.Unlock()
End Try
End Sub
Please note on the code above it is also possible to use something like the following for locking and getting the variable into your collection
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(var)
But it includes an extra step so it is not the most elegant solution. Additionally, you may notice that in the Finally block. This is not required as Microsoft a€˜promisesa€™ in their documentation that variables are automatically unlocked when the execution of the script task stops. So you can trust that will happen or call it explicitly like me just so that you will sleep better at night.
Hopefully, this helps out some of my colleagues out there that may be struggling finding a good outline of this in the documentation.
