If you’ve written a SQL Server 2005 Integration Services (SSIS) package that uses variables, you may have occasion to manipulate those variables within a Visual Basic script task. Unlike much of SSIS, there’s not really a visual editor to help you with this part. In my opinion, the process is not very obvious, so I’m documenting it here in case anybody else might find it useful.
Per my instructions about creating variables in SSIS, create an SSIS package with a new variable. In my case, I call it Counter.
Then add a Script Task to the package and double-click on the task — that will pop up the Script Task Editor dialog box. Select the Script panel from the list on the left, and it will show you a screen like this:
You’ll see a number of parameters you can fill in, but notice the two at the bottom: ReadOnlyVariables and ReadWriteVariables. This is one area where SSIS falls down — it’s missing a widget to select variables; instead you have to (gasp) manually type in the variables you want (comma-separated if there’s more than one). I want to read and write the variable I created earlier, so I put its name in the ReadWriteVariables field.
On this same screen towards the bottom right, you’ll see a button labelled Design Script…. When you first click that button, it opens a little editor window and fills in the basic skeleton for a Script Task:
Within that script you’ll see the following line:
' Add your code here
At that location, put the following code:
MsgBox("Before: " & Dts.Variables("Counter").Value.ToString())
Dts.Variables("Counter").Value = 20MsgBox("After: " & Dts.Variables("Counter").Value.ToString())
Don’t delete the
Dts.TaskResult = Dts.Results.Success
that was automatically inserted into the main method. It tells SSIS that this script finished successfully, which is what we want for this simple example.
Now do a File | Save on the editor window or just close the window to save your code. Then click the green Debug arrow on the toolbar (or select Debug | Start Debugging from the menus). You should get two message boxes: first showing the initial value of your variable (in my case, I set the value to 0) and then the second showing the value set in your script.
This is really it. It’s surprisingly simple, but I wish it were a bit more visual and involved less code writing. Though I suppose by definition this is in a Script Task, so you are expected to be able to do a little programming.
Additional resources about SSIS on TechRepublic
- SQL Server 2005 Integration Services makes a good first impression
- SSIS has a strange little bug with set rowcount
- Creating variables in SQL Server 2005 Integration Services
- How to import an Excel file into SQL Server 2005 using Integration Services
- Using system variables in SQL Server 2005 Integration Services applications
- Using user-defined variables in SQL Server 2005 Integration Services packages