January 31, 2014 at 1:00 pm
Is it possible to set a SSIS string to only the name of a database connection manager or even the connection string it uses ? I don't see an option for that in Evaluate as expression property of ssis variable.
January 31, 2014 at 1:37 pm
ShinyBaldHead (1/31/2014)
Is it possible to set a SSIS string to only the name of a database connection manager or even the connection string it uses ? I don't see an option for that in Evaluate as expression property of ssis variable.
What are you trying to do, an on the fly swap of an OLEDB source/destination?
You need to swap the data source around, not the internal connection from the object to the datasource. Note that if your metadata isn't exactly the same between the two, you're going to get errors.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 31, 2014 at 2:23 pm
ShinyBaldHead (1/31/2014)
Is it possible to set a SSIS string to only the name of a database connection manager or even the connection string it uses ? I don't see an option for that in Evaluate as expression property of ssis variable.
It would help if we know more about what you want to achieve.
You can set the name or the connection string of a connection using an expression, so you could set those in a variable, and then put that variable in the expression. Whether that works for you depends on what you want
January 31, 2014 at 2:25 pm
This is what i meant -
SSIS Connection manager name = SQL_DBASE
SSIS String str_managerName = SSIS Connection manager name: Can you do this dynamically in SSIS ?
February 3, 2014 at 7:21 am
You still haven't told us WHY, which would help with the what.
Here is the basic problem. By trying to set you variable to a connection name via expression, you are IMPLYING the following
1) Your package has more than one connection
2) The variable needs to be named after different connections at different times.
3) The same variable needs to be used at those different times.
So first of all, are all those things true?
If so, what you have not told us is how you want to pick which connection to associate with a variable at any given time, and what you do know about that connection at that time (how it can be profiled).
February 3, 2014 at 11:24 am
Nevyn (2/3/2014)
You still haven't told us WHY, which would help with the what.Here is the basic problem. By trying to set you variable to a connection name via expression, you are IMPLYING the following
1) Your package has more than one connection
2) The variable needs to be named after different connections at different times.
3) The same variable needs to be used at those different times.
So first of all, are all those things true?
If so, what you have not told us is how you want to pick which connection to associate with a variable at any given time, and what you do know about that connection at that time (how it can be profiled).
I only want to set a SSIS variable = name of only one connection. If you change the name of that connection, then the value of variable is reset at runtime. Does that make sense ?
February 3, 2014 at 12:23 pm
ShinyBaldHead (2/3/2014)
I only want to set a SSIS variable = name of only one connection. If you change the name of that connection, then the value of variable is reset at runtime. Does that make sense ?
Yes, it does. I don't know why you'd want it, but yes.
It's difficult. You actually have to backfill the variable by opening up the SSIS package programatically and setting it via a script task. I don't have the method handy but I can probably locate it at some point, will take a bunch of digging around the interwebs.
There's no easy method to get at it, however, which is why you're having so much trouble with it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 3, 2014 at 12:33 pm
I guess my question is: how is the name of the connection getting changed? By a user editing the package? By a script in the package? Other means?
If it is just a user changing it, you can use the method I suggested above, and just make sure its well documented.
1) create a connectionname string variable with the connection's current name
2) go to the properties of the connection and set up an expression for the name property to equal the variable value.
3) Now, whenever you would change the name of the connection, change the variable instead and the connection name will automatically change (and you can refer to it in the variable.
Will that work? If not, I will await Craig's answer with you because I have not done it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply