Variables in SSIS

Question

Variables in SSIS

Danb7183

SSCrazy

Points: 2098
More actions
October 23, 2008 at 4:50 am

#154845

Morning,
I've been working on a project for the past month or so and for some reason Variables are really stumping me in SSIS! I'm relatively new to the program and any helpful info would be much appreciated.
Here is a brief explanation as to what I am trying to do in regards to variables (in sequential order):
1.) Select the Maximum ID from a SQL 2005 table and store it in an appropriately named variable MaxArticleID
2.) Once this article is stored send a request to an HTTP site with the ID tagged on the end to represent the latest batch of data from the website (its a news feeder essentially)
Now, I have no problem retrieving the data from the website and loading into SQL, that bit is all covered. The bit that is really frustrating me is the reading of the data from either SQL or a text file (i've tried both using the Script Task control component) and passing this to another script task to query the website for the data with the relevant ID to receive data.
Are variables cleared from task to task? I am finding that I can read in the MaxArticleID without a problem and even display it in a Message box without a problem. But when it actually comes to writing it to a variable it disspears.
I have tried two separate approaches to updating the variable (both in a script task):
Either
Dts.Variables("NewMaxArticleID").Value = Dts.Variables("MaxArticleID").Value
OR
Dim Vars As Variables
Dts.VariableDispenser.LockForRead("User::MaxArticleID")
Dts.VariableDispenser.LockForWrite("User::NewMaxArticleID")
Dts.VariableDispenser.GetVariables(Vars)
Vars.Item("User::NewMaxArticleID").Value = Vars.Item("User::MaxArticleID").Value
Vars.Unlock()
And neither appear to be working. What am I missing? Or is this not possible (which I find really really unlikely)
Please help as i'm beginning to tear my hair out in frustration as to why it is so blooming hard to do something so apparently easy!
If any more info is needed please say,
Thanks in advance,
Dan

Viewing 5 posts - 1 through 4 (of 4 total)

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

VG-619426 Hall of Fame Points: 3914 More actions · Answer 1

Try to declare a local variable, inside the script and assign global variables value to it and use this variable to update or whatever you want to do..

You have to add global variables to script transformation editor, readwritevariables

Ex:

Dim sBatchcontrolID As String = Me.Variables.gBatchControlID

Row.BatchControlID = sBatchcontrolID --I am updating my table column here...

VG

Danb7183 SSCrazy Points: 2098 More actions · Answer 2

Thanks a lot VG.

I'm going to try this now. My biggest concern is the SQL Task at the beginning which is grabbing the Maximum ID is not storing the result set (single row) to the Variable name User::MaxArticleID (which I thought it should be) So in turn the script task afterwards will not be able to read the data?

I did add the NewMaxArticleID to the Readwrite and MaxArticleID to ReadOnly variables on the script task.

nkjatamoni SSC Rookie Points: 45 More actions · Answer 3

incase if you haven't figured it yet, this can be done efficiently with an execute sql task.

select "result set" as "single row",

write your sql statement like "select max(id) as id from table_xyz"

then go to result set tab, select result name as "id" and select variable name and you are done

Bob Pinella SSCommitted Points: 1737 More actions · Answer 4

Hello,

It's also worth noting that when declaring variables, the scope of the variable is dependent upon where you recently clicked your mouse. For instance, if you click the background canvas and then choose SSIS -> Variables -> Add Variable, you will notice that the scope of this variable is "package". If you click on a specific task inside the package, then choose SSIS -> Variables -> Add Variable, the scope will be the selected task name. Therefore to ensure your variable is available to the whole package, make sure the scope says "package" and make sure to include the variable as a result set of a task, or in the ReadWriteVariables section of a Script Task.

Bob Pinella