I was reading through the following post regarding dynamic connection strings:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=812814&SiteID=17
In this posting they talk about setting up a configuration file to setup the connection string, but I am not sure how to apply that connection string to my connection manager. When I go into my configuration file, I can see the connection string, and when I go into the advanced properties of the connection manager, under the "Named ConnectionString\FileName" property, I set the property value to the name of my configuration file, but when I test the connection I get an error that the connection string property has not been initialized.
Can someone please assist me with identifying what I am doing incorrectly?
Thanks in advance for the assistance!
Craig
Hi Craig,
It sounds like you might be missing a step. In the post you linked, they are setting a variable through a configuration file, and then using that variable in an expression to set the connection's ConnectionString. Did you setup the expression properly?
Jaime's blog has an entry on how to set expressions.
Hope that helps!
~Matt
|||
Hi Matt,
Thanks for the reply. I checked out the link that you sent me. It looks like he is setting the data source connection string for a flat file dynamically... When I go into the Connection Manager for the Ole Db, there is no expression property in which to set up. There is, however, a File Name property under the "Named ConnectionString" group, which I put in the value of <PATH>\SSIS_Config.dtsConfig.
I am trying to set the connection string for the destination SQL table dynamically (we basically have 3 environments - Development, Staging, and Production). When the packages are pushed to the staging environment, we want to be able to change the connection string in the configutation file and have all packages use the configured connection string.
Please let me know if you have any further questions.
Thanks!
Craig
|||It's a little confusing, but the expressions don't show up in the connection manager editor window you get when you double click on the connection name - you have to view the connection in the "Properties" window. Right click on you connection and select Properties, or select the connection when the properties window is in view.
I'm not too familiar with the "Named ConnectionString" property group, but I think you can accomplish what you're trying to do by setting expressions for the InitialCatalog property (and maybe ServerName if your host changes based on the staging environment).
Post if you have trouble finding it, or it's not working for you.. I can try to post some screen shots.
~Matt
|||
Matt,
Thanks again for the reply! I did find the expressions for the Ole Db connection manager (just as you said, not by double-clicking, but by clicking on the connection manager, and expanding the properties window). :-)
So, here's my issue... I created a global variable as a string data type, and set it's value to the path of the config file. I then opened the expressions window, and selected the ConnectionString property, and set it's value to @.[User::Config_ConnectionString]. When I evaluate the expression, i get the path of the config file.
Am I missing another step? If you don't mind putting some screen shots together, you can email me directly at craigster1976@.msn.con, so you don't have to worry about trying to post them here. I think i'm pretty close - just missing one or two steps.
Thanks for all of your help, and your speedy responses!
Craig
|||Hi Craig,
The variable should be the actual value, not the path to the configuration file.
You should take a look at this article - Easy Package Configuration. It appears they are doing what you're trying to accomplish without using expressions. Expressions are more useful when you're building up connection strings programmatically. If you're setting the full server / initial catalog values in your configuration, you might want to take this approach instead.
~Matt
|||
Good morning from snowy Colorado!
I am kind of confused... :-( I have set up my configuration file, which contains my connection string (the link in your last posting gave instructions on how to set up the confguration file, and that worked fine). Now, I need to retrieve the value from the config file, and set the connection string for my connection mamager, and I am not sure how to accomplish this... I have tried several variations... I tried setting up an environment variable, pointing it to my Xml config file, but I cannot find any documentation on how to pull the value out, and use that value in my package. I have seen a lot of postings where the config file is used to programmatically pull the value from the config file, then set a variable in the package through code, but we would rather not do that... All of our packages are going to be running through scheduled jobs, and we don't want to write a program that kicks off the package. However, rather than manually changing the connection string every time we move from Dev to Prod, we want this value to come from the config file - we would just change that config file value for the new environment that we will be running in. I have read through alomost all of Jamie's blog's regarding SSIS, and haven't found one that addresses what we are trying to accomplish. It seems that there is a plethera of information on setting up the config file, but not pulling values from it! Anyway... If you can provide any further insight, I would be most appreciative.
Thanks, agian, for the help and the time!
Craig
|||If you set up a configuration file then you don't have to do anything in your package other than tell your package to use it (which you do via the SSIS menu in BIDS).
You do not have to do anything explicit in your package to make a value from the referenced configuration file appear for a particular property (i.e. "pulling from it" as you term it). If you have a configuration set up in that configuration file for a particular property then it just happens as a matter of course.
-Jamie
|||
Jamie...
Well, now that I feel like a complete moron! To test, what I did was set up the configuration file to point to our development DB server. I then setup the connection manager to point to my localhost. When I exected the package (just as you said) "it just happened as a matter of course"... All the records were pumped into the development DB, just as they were supposed to.
On a side note, I have really enjoyed your blogs, and have learned a ton from them, so thanks for putting that information into your blogs for us to learn from! Just as a suggestion, maybe put together a blog regarding the use of the configuration files so people like me don't spin our wheels for a day trying to figure something out so remedial!
Thanks, again! Have a nice day!
Craig
|||Craigster wrote:
Jamie...
Well, now that I feel like a complete moron! To test, what I did was set up the configuration file to point to our development DB server. I then setup the connection manager to point to my localhost. When I exected the package (just as you said) "it just happened as a matter of course"... All the records were pumped into the development DB, just as they were supposed to.
OK cool. Good to know. Glad you got it working.
On a side note, I have really enjoyed your blogs, and have learned a ton from them, so thanks for putting that information into your blogs for us to learn from!
My pleasure.. Its good to know that they're appreciated, believe me.
Just as a suggestion, maybe put together a blog regarding the use of the configuration files so people like me don't spin our wheels for a day trying to figure something out so remedial!
All suggestions readily accepted. I'll put it on my "things to do" list.
cheers
-Jamie
No comments:
Post a Comment