How to change package level variable value in script task?

Question

Post reply

How to change package level variable value in script task?

Kari Suresh

Hall of Fame

Points: 3712
More actions
May 13, 2010 at 6:24 am

#218891

Hi Buddies,
I have a SSIS package that has almost 60 tasks including Script tasks and Execute SQL tasks.
Tasks order is:
ST1A-->EST1-->ST1B--> ST2A-->EST2-->ST2B--> ST3A-->EST3-->ST3B--> and so on..
Here ST is Script task and EST is Execute SQL task.
sourceTable is a variable and this is been using throughout the package. I added this in Variables window that has data type as String and Value is "SE_DatabaseServer". I have added sourceTable variable in ReadWriteVariables of each Script task.
I have the following scenario:
Need to change sourceTable variable value in the task ST1A and that value need to be used in the task ST1B. I changed the variable value like this: Dts.Variables("sourceTable").Value = "BI_DatabaseServer"
I can change the value of the variable by using above code line, but that is only for that task only that is ST1A and the old value is coming for the task ST1B.
How can I get the new sourceTable variable value to ST1B task? Can anybody help me?
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

Should work as you hope. What is the scope of your variable? It should be 'Package'.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 2

Yes, it is package level variable. But it is resetting to old value i.e.: "SE_DatabaseServer".

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

Kari Suresh (5/13/2010)
Yes, it is package level variable. But it is resetting to old value i.e.: "SE_DatabaseServer".

Sorry, maybe I should have taken more notice of the subject of the thread & I wouldn't have had to ask :crazy:

Is User::sourceTable listed in the ReadWrite variables list for the script task?

How do you know that the script task has set the variable?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 4

I given just like sourceTable in the ReadWrite variables list for the script task.

And I used MsgBox to know the value like:

MsgBox(Dts.Variables("sourceTable").Value.ToString())

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Phil Parkin SSC Guru Points: 246979 More actions · Answer 5

Kari Suresh (5/13/2010)
I given just like sourceTable in the ReadWrite variables list for the script task.
And I used MsgBox to know the value like:
MsgBox(Dts.Variables("sourceTable").Value.ToString())

Replace "sourceTable" with "User::sourceTable" in your script and in the ReadWriteVariables list and try again.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 6

Replaced with "User::sourceTable" and tried, but again the old value only it is taking.

Now I removed "sourceTable" value from Variables window.

Got error saying Incorrect syntax near 'FROM' ("SELECT * FROM ").

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Phil Parkin SSC Guru Points: 246979 More actions · Answer 7

I don't know what's happening, that works for me. Anyone else got any ideas?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel