Kirk Haselden

Easier variable access...

Well, by now you know that the next CTP build, IDW11 is available.

There are a lot of bug fixes and suggested changes in this latest drop for SSIS.

Here's a quick one some of you have been asking for: a simpler method of accessing variables in the script task and transform.

The way you access them is slightly different in the script transform than the task. I'll show how to access both in this post.

First an explanation of the problem:

SSIS is an inherantly multi-threaded application. It's possible that at any time, more than one logical/physical thread is attempting to access a variable at a time. Given the right circumstances, it's possible to corrupt the value of the variable. That's not good. So, the IS team created an object called a VariableDispenser. It handles the details of locking and unlocking access to variables for reading a writing. Problem is, it's not very intuitive to use. So folks asked for a different/better way to get at variables:

The old way in the script task:

Create a variable. I called mine TestVar.

Dim vars As Variables

Dim var As Variable

Dts.VariableDispenser.LockForRead("TestVar")

Dts.VariableDispenser.GetVariables(vars)

var = vars.Item(0)

The new way in the script task for IDW 11:

Create a variable.

Select the script task in the designer. Go to the properties grid. Type in “TestVar” in the ReadOnlyVariables property.

Now the code to access the variable looks like this:

Dim var As Variable = Dts.Variables("TestVar")

As you can see, access is a lot simpler.

In the script transform, it looks something like this.

The new way in the script transform for IDW 11:

Create the variable. Add the variable to the ReadOnlyVariables or ReadWriteVariables. Then the variable is accessible like this.

Variables.TestVar

That's it.

You can still use the old method if you like. So if you are accessing variables with the VariableDispenser now, this is still compatible. This just makes it simpler in code. You may also specify as many variables as you like in the property grid, separated by commas.

Hope this helps. If you try this, let me know what you think. Did it make things easier? Is it simpler?

Thanks,

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden