Can't reference SSIS variables in script task

Question

I have a very simple SSIS package and an absurdly simple Script task

    var x = Dts.Variables["User::OrderPath"];

That fails with:

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

I have a variable, OrderPath, that is scoped to the package. Any time I try to add the variable to the script's ReadOnlyVariables, it disappears whenever I execute the task.

This really shouldn't be this difficult so I assume I'm missing something monumentally basic. Any idea what that might be?

You click to add the variable into the readonly collection and after doing whatever with the task, the variable disappears? Also, what if you attempt to access the variable by its ordinal position x=Dts.Variables[0] Final comment, do note that x will be an instance of an SSIS Variable. Generally, you're looking to access the Value property of that Variable. — billinkc
Thansk, Bill. Yes, it disappears, as odd or wrong as that may seem. I've not yet tried ordinal reference, we'll see how that goes. Thanks on the Value point. Yes, that's what I'm ultimately doing, but by the time of posting was trying to distill it down as simply as possible. — John Spiegel
That smells very wrong. You might want to ensure you have the latest and greatest bits for your visual studio, sql server and windows OS. — billinkc

Edmund Schweppe Edmund Schweppe · Accepted Answer · 2013-11-17T22:13:05

When accessing variables through the Dts.Variables collection, you do not prefix the variable name with the namespace; thus

var x = Dts.Variables["User::OrderPath"];

fails, while

var x = Dts.Variables["OrderPath"];

succeeds. (Assuming, of course, that OrderPath is added to either the ReadWriteVariables or ReadOnlyVariables task properties.)

See Using Variables in the Script Task in MSDN for another example.

Can't reference SSIS variables in script task

1 Answers