ScriptComponent with multiple ReadOnly variables

I'm having trouble with a script component in which I'm trying to use two ReadOnlyVariables. If I use only one of the two variables, everything works without issue. If I use both of the variables (as part of a comma-delimited list) I get the following:

The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.

I don't believe the variables themselves are the problem. Both are scoped to the package level and I can use either of them if I have it as the only variable. Seems bug-like, but thought I'd get some ideas before pursuing that route.
When you type, "Me.Variables." in your code, what pops up in the list?|||I can't even get to the script if both variables are in the list. I get the message in my first post when I click the Design Script button.

I can move one of them to ReadWrite variables and then typing "Me.Variables" brings up both. If I use it as R/W, however, I get the PostExecute() error at run time.|||

mr_superlove wrote:

I can't even get to the script if both variables are in the list. I get the message in my first post when I click the Design Script button.

I can move one of them to ReadWrite variables and then typing "Me.Variables" brings up both. If I use it as R/W, however, I get the PostExecute() error at run time.

You are using the comma "," right?|||Yes. I have Script Tasks where I'm using a comma-delimited list and have no trouble with them.

I took a look at the raw dtsx in a text editor. I noticed a node property for the variable titled "isarray" and it's set to false. I thought perhaps it should be "true". However the package wouldn't load after manually changing it, so I'm back to being stuck.|||

mr_superlove wrote:

Yes. I have Script Tasks where I'm using a comma-delimited list and have no trouble with them.

I took a look at the raw dtsx in a text editor. I noticed a node property for the variable titled "isarray" and it's set to false. I thought perhaps it should be "true". However the package wouldn't load after manually changing it, so I'm back to being stuck.

It works great for me. If I type in "myvar1,myvar2" and they do, in fact, exist at the component level (are scoped appropriately), then things work. If I remove the comma, or mistype the var name, I get the error you indicated. I'm sure you've done it already, but double check the spelling and the scope of the variables you are listing in the ReadOnlyVariables section.

Phil|||Are you referencing a variable that has an expression in it that could be causing scope issues, or something?|||

mr_superlove wrote:

Yes. I have Script Tasks where I'm using a comma-delimited list and have no trouble with them.

I took a look at the raw dtsx in a text editor. I noticed a node property for the variable titled "isarray" and it's set to false. I thought perhaps it should be "true". However the package wouldn't load after manually changing it, so I'm back to being stuck.

Just a small thing. There's no space after the comma is there?

If there is, there shouldn't be. People have been caught out by that before.

I know its simple but I just wanted to check.

-Jamie

|||Phil, I want to go ahead and apologize for wasting your time. The moment I saw "myvar1,myvar2" I realized that the extra space I put between my comma and second variable name was the problem and it was.

I'm going to chalk this up to being Monday morning. I appreciate the attention you paid to it.|||

mr_superlove wrote:

Phil, I want to go ahead and apologize for wasting your time. The moment I saw "myvar1,myvar2" I realized that the extra space I put between my comma and second variable name was the problem and it was.

I'm going to chalk this up to being Monday morning. I appreciate the attention you paid to it.

The interface for this is really bad isn't it? They should provide a combo box for you to select the variables that you want. Or at least explicitly check that the syntax of the property value is correct.

Would you mind raising something at Connect (http://connect.microsoft.com) asking for some changes to this.

-Jamie

Script Task Variables

script task: there should be another way to select variables than the comma seperated list

One has to type in a whole list of variables, hoping not to make any mistakes

IntelliSense for example?

But hey, I'm not complaining...

greets

There is! You can do it in code.

Writing to a variable from a script task
(http://blogs.conchango.com/jamiethomson/archive/2005/02/09/964.aspx)

Still no intellisense though!!

I highly recommend you use the code option because this minimises the risk of variable locking. I plan to blog about this soon and have written about it in an upcoming article in SQL Server Standard.

-Jamie

|||

ok, I'll do it your way

thanks

|||

Dear Jamie,

Your method works fine for 1 variable.
I assume you know this, but for other readers of this thread:

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9mref/html/T_Microsoft_SqlServer_Dts_Runtime_VariableDispenser.htm

quoting:

There are two scenarios for using the variable dispenser.

You want just one variable. In this scenario, call LockOneForRead or LockOneForWrite, and a collection with one element is returned.

You want several variables. In this scenario, call LockForRead and LockForWrite several times, one for each variable. This builds up two lists, one list that contains variables for reading and a list of variables for writing. Next, call GetVariables, which gives you a collection that contains all of the locked variables. If GetVariables succeeds, the two lock lists, which are the lists of variable names, not actual locks, is cleared.

To clear the locks, call Unlock on the collection when finished to explicitly release the locks. This unlocks the variables themselves. If GetVariables fails, the lists remain unchanged, and you can call GetVariables again. If you still do not succeed, call Reset to clear the lists and bring the variable dispenser back to its initial state.

Cheers,

Tom