Variables Access in SSIS Script Task

Question

Variables Access in SSIS Script Task

Anand Kannan

SSC Eights!

Points: 801
More actions
September 17, 2009 at 5:04 am

#204698

Hello,
I'm trying to access the variables that are declared with package level scope in a Script task using VB.NET as Scripting Language.
I have the variable names and its corresponding values stored in a parameter table.
I'm trying to access the parameter table and assign all the variables & its values stored in the database table to the SSIS package variable to be used in the downstream tasks in the package.
I tried the following code:
While sqlResult.Read()
If Dts.VariableDispenser.Contains(sqlResult.GetString(0)) Then
myHashtable.Add(sqlResult.GetString(1), sqlResult.GetString(2))
Dts.VariableDispenser.LockForWrite(sqlResult.GetString(0))
End If
End While
Dim myPkgVariables As Variables
Dts.VariableDispenser.GetVariables(myPkgVariables)
Dim myDictEntry As DictionaryEntry
For Each myDictEntry In myHashtable
myPkgVariables(myDictEntry.Key).Value = myDictEntry.Value
Next
myPkgVariables.Unlock()
sqlResult.GetString(0) --> Contains the name of the variable prefixed by "User::".
sqlResult.GetString(1) --> Contains the exact name of the SSIS variable
sqlResult.GetString(2) --> Contains the value for the variable to be set at runtime
Adequate care is taken as variable names are case sensitive.
When I checked for the variable collection count (Dts.Variables.Count()), it returned 0 inspite of the variables created with package level scope. I tried including & excluding the variable list in the ReadWriteVariables property of the script task. But the result is the same.
Can someone provide answer to the following questions?
1. Why the variable collection is returning a value of 0 all the time?
2. Is this feasible in SSIS?
3. If yes to Qn #2 whats the best approach.
Thanks in advance,
Anand

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

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

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Hey Anand,

In the variables container (populated by the Dispenser.GetVariables method), am reasonaly sure the variables still need to be referenced by the full path not just the name. Try setting your hashtable key to SqlResult(0) instead of 1?

HTH,

Steve.

Anand Kannan SSC Eights! Points: 801 More actions · Answer 2

Hey Steve,

Thanks for your time & suggestion. But no luck still.

I guess the real problem over here is that the variable count is returning a value of 0, meaning the declared variables at package level is not being recognized inside the script task; which is really surprising for me. I think I'm missing something over here. If this is resolved, I believe I'm all set unless and until it is not a tool constraint with SSIS.

Thanks,

Anand

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

For what it's worth, when I tested the following method in a script, the only way I could get it to return 0 was to have no variables declared in either Read or ReadWrite. With variables declared in both, it returned the count across both.

Public Sub arnand()

MessageBox.Show(Dts.Variables.Count.ToString())

End Sub

If the variables are definitely defined in ReadWrite, then, really not sure why they wouldn't be available???

Steve.

SSRfromChennai Ten Centuries Points: 1120 More actions · Answer 4

Hi

I would like to get the SSIS Meta data like what are the Tasks it contains, Connection managers, variables inside for a SSIS Package. Will your Vb.net code can help ?? Do we have to use any SSIS API function to get ? I am familiar with VB 6.0 but not with vb.net

Anand Kannan SSC Eights! Points: 801 More actions · Answer 5

Hey Steve,

I've got this resolved. For some reason, I started to go with variablesdispenser which was actually not working as I expected. I changed the approach to dts.variables and it started working like a charm.

Cheers,

Anand Kannan