August 25, 2008 at 7:05 am
I posted this on the other ssis forum with no reply, try my luck here:
I am migrating a dts package to ssis package. In my dts package, there is a ActiveX script to retrieve metadata from database and create global variables to hold these data.
But it seems in ssis Script task, we can only modify an existing package variable and unable to create new variables at runtime. How do we handle this task in SSIS package? How do we create variables at package runtime?
I am playing with the Object type of variable, try to map it to a hash table which hold all the value pairs metadata, but I dont know how to pass a value from this Object type of variable to sql task.
There must be a work arround. Otherwise it is drawback from 2000.
Thanks Jane
August 25, 2008 at 7:20 am
Jane,
You do have to create the variable at package creation time. However, you can wait to populate it, or repopulate it, at run time. So the workaround is to create every variable you might conceivably need and then use either Execute SQL Tasks or Execute Script Tasks (or any number of other tasks) to set the variable up.
But the two mentioned tasks are the most commonly used.
What variable type are you trying to populate? What's the source?
August 25, 2008 at 11:24 am
The issue here is we have significant amount of variables created by ActiveX script at DTS runtime, they are then feeding different Execute SQL tasks. If I have to create all the variables at design time for SSIS package, it takes time, I will have to apply that to all our packages. Wonder if there is a work around.
So I try use the Object type of variable to hold all the variables value pairs, but again I can only pass a package variable to a execute sql task, I can not pass an expression to it, i.e. ((hashtable)dts.Variables("variableHashtable"))("oneVariable").toString
where variableHashtable is a package level object type of variable which maps to a hashtable to hold all variable pairs (key/value), and oneVariable is one of the key from the hashtable.
Jane
August 25, 2008 at 11:38 am
Jane,
You might be able to create the variables in a SQL Table that is accessed by a config file. That way, you don't have to create each variable inside the individual packages but you tell SSIS to go to the environment table to pull them as needed when you run your script task.
There is an "environment variable" configuration type for the config packages, but I haven't played with it beyond our initial adaptation of SQL 2k5 (over a year ago).
August 25, 2008 at 11:39 am
Opps. Ignore the "environment variable" comment. I think you'd have to choose "SQL Server" in that dialogue box.
August 25, 2008 at 12:04 pm
Brandie,
Thanks for replying. As I understand package configuration will only assign values to the variables, you still have to create the variables (declaration) at design time with the Variables window. That is the step I want to bypass, as in our activeX script, the variables (declaration and assignment) are created at runtime.
Jane
August 25, 2008 at 12:57 pm
I've looked around and can't seem to find anything about creating the variables at run time. I'm sorry. The closest I can come is Dynamic SQL which you could stick in a single variable and run, but might not work for your scenario if you're not executing stored procedures or T-SQL Statements.
I'm sorry. Looks like you'll have to do this the hard way. Unless you posts on MS's forum and someone comes up with a different answer.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply