March 18, 2010 at 12:01 pm
SSIS 2008. We don't have a SSIS 2008 forum yet, so this seems like the best place for this question.
Here's my scenario. I have a table that specifies a bunch of customer db's. Each row has the server ip, user name, and password that I need to connect to. So I have a package that loops through them all, and an empty OLE DB connection that serves as the place holder for each site I want to connect to. I want to specify the connection properties dynamically. The server and user name are easy, they are available as expressions, so I can easily map them to user variables, but there is no password expression. I don't want to have to map the entire connection string to a user var because then I'll have to do all kinds of string gymnastics to build a connection string. There must be a better way!
Thanks!
.
March 18, 2010 at 12:08 pm
Instead of defining expressions for the individual elements of the connection, set all of the dynamic connection properties using the connectionstring property. Here you can set server, user, password, etc.
March 18, 2010 at 12:21 pm
Thanks! Got it, that makes sense, but then how do I set these values at run time during each iteration of the loop? The config data is only loaded once, at startup.
.
March 18, 2010 at 12:26 pm
Each iteration of your loop will grab the connection info for the current connection and set the package level variables. When the ole db connection manager is invoked during that iteration, the expression that sets the connection string values with the package level variables will handle the dynamic connection.
So you're not going to be pulling the values in from a config file (that only happens once). Your loop iterations will pull the values from your table.
March 18, 2010 at 1:26 pm
Great, thanks. That makes sense.
One more thing, how can I get BIDS to stop trying to validate my dang dynamic connection at design time. Very annoying. It does not need to be validated until runtime.
Thanks a lot!
.
March 18, 2010 at 1:28 pm
I usually default the variable values to my test DB. This allows the package to pass validation instead of failing as it does before the variables are populated.
March 18, 2010 at 11:45 pm
I thought I understood what you meant regarding "setting the package level variables", but now that I'm working on it, I'm confused. Doesn't that leave me right back where I started? So now I have all the connection info. I need in my config file, and I can move that into my runtime environment using user vars, but in the end, since the OLE DB connection doesn't expose the password, I'm not sure I understand how I'm supposed to get to it to set it.
When you say "package level vars", maybe you don't mean user vars? Maybe that's where I'm confused. I'm pretty new to SSIS (as I'm sure you can tell). The only way I know of to set runtime values is literaly via properties, and dynamically using either user vars and/or expression substitutions. Is there another way to get to that password that I'm missing?
Using an expression to set the entire connect string isn't so bad. Maybe I should just go that route?
Sorry, I thought I understood you, but I guess not. :blush:
.
March 19, 2010 at 10:29 am
Yes, package variables are user variables. Sorry for the confusion.
Like I said in my first reply, you'll have to set the Connection String property to embed the dynamic passowrd into the connection manager object. In the absense of an indiduat property that you can set, this is the only way to go.
I would recommend setting up your connection using SQL Server authentication and then copying the ConnectionString property from the properties window. Go to the expression builder, paste the Connection String into the expression editor, repalce values w/ variables as needed, and then add in a password=variable entry in the string with with this format:
Pwd=myPassword;
March 19, 2010 at 8:31 pm
Got, works great! Thanks for your patience!
.
March 22, 2010 at 10:01 am
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply