Data Management

Using SQL Server 2005 Integration Services variables from a Script Task

While it's not the best documented feature out there, it's actually pretty easy to use SQL Server 2005 Integration Services variables from within a Script Task. Here's a quick step-by-step example to get you started.

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.

Create SSIS variable and Script Task

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:

Edit the script task parameters

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:

Script VBA editor

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 = 20

MsgBox("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

Editor's Picks

Free Newsletters, In your Inbox