How to manage design time connection strings?
-
I understand there are various configuration methods (expressions with master package variables, configuration files, etc) to manage connection strings at run time but how can I manage Connection Strings at design time?
I work on a package on my server where the servername might be Whatever\Instance1 but I want to share this development with remote developers where the server name / instance will be different.
Each time we extract the package from source control, we have to edit the connections manually...
Is there a smarter way to do this?
The only one I can think of is to use "Localhost" and the same instance name...
Thanks
Eric
-
We have a small ETLConfig database on the local server. This database contains all connection strings in a small table.
This way you have the same connection for any deployment, and connection strings in a table are easy to maintain from a central location.
-
But does not that only affect the runtime?
At design time, these connections are used "as is" to check the metadata, etc...
It's fine if everyone develops on the same server but not if we used more than one development server.
Am I missing something or maybe I am too difficult to please?
-
That is a prefectly valid question.
Yes, connection strings in the database work only for run time.
If you need to work with connections during design time, you will be probably better off if you have a shared dtsConfig file. However, based on your explanation, this shared file will be different for each environment and therefore for each deployment.
-
Revenant (2/2/2012)
That is a prefectly valid question.Yes, connection strings in the database work only for run time.
If you need to work with connections during design time, you will be probably better off if you have a shared dtsConfig file. However, based on your explanation, this shared file will be different for each environment and therefore for each deployment.
Setting up a local config table on every development computer will work, if combined with an environment variable that points SSIS to that config table.
When you open the package in BIDS, SSIS will fetch the data from the config table and configure the package.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
I like that!
Indeed, my problem was really BIDS.
I don't have the time just now but I'll give that a shot ASAP.
Many thanks
Eric:-D
-
As you'd have expected, it did the trick.
I created an XML configuration file to setup some variables in a "parent" package and derived everything else from there (via expressions).
Another thing surprises me, if i may...
When running my package at runtime, I specified a different config file on the command line but it looks like the package is still trying to load the config from the design time file as well (after loading from that new file).
It works but produces a warning during execution and sounds weird.
Am I doing anything wrong? :doze:
Is there a way to setup a design time configuration file so that it is not attempted at runtime?
Thanks
Eric
-
You're not doing anything wrong.
Read the section about Understanding How SSIS Package Configurations Are Applied at Run Time. Then read it again. And again. And probably again until it makes sense
The order in which configurations are applied is a bit ... tricky, to say the least.
If you do not want design time configurations to be applied, it is best to remove them entirely from the package. Or you could change the location of your "design time" config file using a command line option.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
In an environment where there are both 2005 and 2008 versions on the same server.
Am I right to assume that the SSIS Runtime IS 2008 even if I schedule my package from within the 2005 SQL Agent?
In which case, my runtime configuration behaviour is that of 2008 rather than 2005!
-
I am struggling a bit to define a configuration strategy because i want to cope with the following cases and it's becoming complicated...
- I may have developers working on different servers (or their laptop)
- the development environments are likely to have both 2005, 2008 and maybe even 2008R2 on the same box
- the Production has only SQL 2005 installed
I started with a config file setting up variables used in expressions to set connection strings for my master package and child packages using parent variables for configuration but I seem to be struggling with that, maybe because I did not realise I have a 2008 behaviour instead of 2005...
It looks like my design time settings overwrite my config file settings. :crazy:
What approach would you suggest?
-
SQL 2005 can not run 2008 packages, so you should only be developping in BIDS 2005, or migrate to 2008.
There is no difference between 2008 and 2008R2.
I think the easiest method is to have only one configuration. On the development machines, a config table/file is loaded. On the test/prod server you load the configuration from the exact same location. Or you use environment variables to point your configuration to another location. This way you avoid having two configurations at the same time and accidentally having the wrong one loaded.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
I do indeed develop on BIDS 2005 for this project because the Prod target is 2005.
Everything seemed fine on my dev server (aka laptop) but not in a different environment.
As you mentioned I tried to read understand-how-ssis-package-configurations-are-applied
If I understand, it appears that since I have both 2005 and 2008 on the same machine, the behaviour is that of 2008.
My variable is set properly by the configuration file, which in turns probably sets the connection string properly BUT, as per 2008 behaviour, the design time settings overrule and I end up with my design time connection string setting, which is wrong.
Based on this, I should switch to 2008 configuration setting behaviour, but then things would not work in my production environment, where there is ONLY 2005 installed.
So I should choose something that works in both 2005 and 2008.
According to the article above, it should therefore be "/CONF to load run-time configurations for DIFFERENT property values than those set by design-time configurations".
... but I don't quite understand what this means???
In the end, everything has a design time value so this design time value will always overule?
Clearly, I am missing something here...
PS: For my children packages, I "hope" I will be fine because I use parent variables from the master package which seems to be evaluated last. So as long as my parent variables are correct, I should be fine.
-
Normally 2005 packages have the 2005 behaviour and 2008 packages the 2008 behaviour, as each has it's own Integration Services service that loads the appropriate runtime and DTEXEC executable.
This is how I do it in my environment:
1. On the development server there is a config database that has all the config tables containing the connectionstrings used for design time default. (you can also put this config database on your own development computer, if you have SQL Server locally installed).
2. On my own machine, I have an environment variable that contains a connection string to the database containing config table mentioned in step 1. (this can also be a filepath if you use XML config files)
3. In the SSIS package, I have a package configuration that uses this environment variable to set a connection manager called SSIS_Config. In all other package configurations, I use this SSIS_Config connection manager to find the corresponding config tables. So make sure that this environment variable configuration is at the top, as it should be applied first.
4. Thus, when I open a package, the environment variable is loaded and the connection string to the config database is set. In all subsequent package configurations, the connection strings are loaded from their corresponding config tables, using that first connection string to find the config database.
5. On each server (dev - uat - prod), I have an environment variable that points to their own config database.
6. This means, if I put a package on another server and I run it, it will use the environment variable from that server to find the config table on that server that contains the run time connection strings.
I use this set-up all the time, and it never gives me troubles.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Damn!
What had it look even more complex was that I forgot to inherit a variable value from the master package in a sub package and that variable was then used to setup a Connection string via expressions...
AAAAAAAAArrrrrrr!
Many thanks for your help. It was (and still is) much appreciated.
Cheers
Eric
-
Eric Mamet (2/8/2012)
Many thanks for your help. It was (and still is) much appreciated.No problem, glad to help.
I hope everything goes smoothly from now on.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply