blogs.conchango.com

I did one project where all the connection strings were variables. This was nice because the master package could control the connection strings then pass the master package variable to the child package variable.

But the downside was that when you were trying to test a package that another developer wrote, your connection string might not be the same as his. So every time you'd open up someone else's package, it would take about 5 minutes to finish validating all the pieces of the package because the connection strings pointed to a non-existent SQL server.

So on my most current SSIS project, we opted to use configuration files using an env variable to point to the path of the configuration file. That way, I can open someone else's package and it will automatically pick up my connection strings.

Also, I can keep multiple dtsConfig files and swap them in and out when I need to test against the dev database and the QA database. When I was using package variables, I had to constantly retype those connection strings over and over again since often you just test one package manually without running it from the master package.

I think setting the variables based upon configurations would have solved the problems I had with using variables for connection strings.

I've been using expressions for a while.  This has been useful for me because I have worked with a lot of different sources and destinations, but the same basic structure.  I found that using the "Delay Validation" option in the code helps quite a bit to avoid validating that everything exists when I open a package.  It also stops the package from trying to write to the incorrect database or read from the wrong source.

My basic method has been package variables to control individual elements of the connection string where possible.  I have to manually change them when I open the package, but that's just a whole lot easier and faster than using the UI to search for source files or change database names.

-Pete Schott

Why have you got multiple connection managers with the same connection string? I would expect to have one connection per connection string, and use the multiple-instance behaviour of the connection manager to provide individual connections to each task or component as required. Saying that I have found having two connections is useful sometimes when using explicit T-SQL transactions.

One issue that people should be aware of when using variables for sensitive information, is that the value will be clearly visible in any checkpoint file, if used. You can take a view that these files will be secured correctly using NT permissions, but something to bear in mind. Some organisations have strong security rules or guidelines that may prohibit this.

Hi Jamie,

We are using connection string parts in variables and have an expression on the connection manager for just two reasons:

- first

we store a kind of status [dev or productive] in a variable to trigger the right connection strings and source / target database servers from the configuration

- second - and more important

regarduing our ETL-scenario we have to loop through several servers, databases and tables which all have the same metadata and will be activated via a kind of loadtable-configuration.

Using the connection string information in expression enables to modify the source / target and also enables a loop while loading data from different sources to different targets.

We also use expressions for the sql statements though that also will be modifiable during runtime of the package.

That in my opinion simplifies development because you are just talking about source and target and the dedicated servers are triggered via variables and expressions.

cheers,

Markus

Hello,

If I'm following the discussion here correctly, you are saying to store 'Data Source=' in one variable then append the server name then ';Initial Catalog=' in another varible and so on?

My other question is naming conventions for database connections and how to use those in a configuration file and how to easily change those when moving packages to different servers without making changes to the package. Any ideas?  I've done a ton of searching and reading for specifics and there really isn't anything spelled out with specifics.  Or would you even put database connections in the configuration file? Or would each server you could connect to have a seperate configuration file?

Laurie

Hi Jamie,

Thanks so much for the reply.  I've actually read that best practices doc, a few times now (even before you sent this).  And I'm still a little confused...sorry, I have no idea why this is the hardest part for me to grasp.  I understand everything else pretty easily.  So lets say I have a package, package1.  Package1 has 2 database connections, one is an ADO.NET connection and one is an OLE DB connection.  I have named them DEVSERVERNAME.DatabaseName ADO.NET and DEVSERVERNAME.DatabaseName OLE DB in the package.  Each has a different connection string referencing the same server and database in our development environment.  So you said store each of these in their own config files.  What would I call the config files?  My guess would be something like DEVSERVERADONET and DEVSERVEROLEDB?  This is where I get confused.  All these names have to be generic enough to reference in the package so when I change servers, it works.  But what if I have more than one ADONET connection to another database?  I can't name the config with the server name in it because if I reference that server name config in the package it will change when I move to production to PRODSERVERADONET.  And if I want to use the ADONET connection to connect to another database, I can't reuse that connection string.  So that's when I started thinking about storing the different parts of the string like I mention in the first message. And what would you name the variables in the package to keep them generic enough?  It is the idea of keeping things generic that is messing with me I think.  I hope I haven't confused you with all of this.

Thanks again so much for the help!

AHhhhh Haaaa!  So then as the package moves from dev to prod, all I have to do is make sure that in each database connection in the config file has the right server name in the connection string and it will reference the right server without having to make changes in the package.  Perfect!  

The article below was what got me started thinking this way.  Check out the section called Guidelines on using XML configuration files in SSIS. If you leave the server name out, then that makes sense.

http://vyaskn.tripod.com/sql_server_2005_making_ssis_packages_portable.htm

Thanks again so much! I've learned so much from the stuff you have posted.

Laurie

Jamie and others,

Will be of great help by you, if anyone can solve my issue with Package Configurations:

I have initialized few global variables and I want to assign them to the properties of few tasks, log provider and connection managers.

Would you please give me steps how I can achieve this ...

--------------------------------

"furmangg,

Interesting. Well what I am contemplating doing is a mixture of the two. I store the conn strs in variables and, rather than pass the values from a parent package, I set those variables using configurations. Would that solve the problems that you encountered?

-Jamie

December 1, 2006 8:10 PM "

Hi, Jamie and furmangg,

  Thanks for your support. It was a great help.

Subhash Subramanyam

You don't need to do this at all, you're making it too complicated as was I in the begining.  Do NOT add Connection Managers within each Package!  All you need to do is add Connections under the "Data Sources" folder of the SSIS Solution.  I usually add two DataSources for each Database I use, one OLEDB and one .NET Connection.  

Once you have all your Data Sources added, NOW you can add your Connection Managers, however, you need to select the option "New Connection from Data Source..." from the Connection Manager menu.

This will basically allow you to have the same exact connection in every Package and be able to modify the connection string in one location; the Data Source!  Now, when you move your Solution to production, you don't need to change a bunch of Variables, you just modify the Data Source connection properties!  I'm positive this can be done with configuration files, but I haven't tried it yet.

Tim-

That's really an interesting idea. I've never uses the Data Sources folder in an SSIS project so I tried it out today. I created a data source and created a connection manager in a package based upon it. So far so good. Now I change my data source. If the package is open, it automatically updates the connection string on the connection manager. If the package is closed, it doesn't automatically update the connection string on the connection manager. When you open up that package, though, you get a Synchronize Connection Strings dialog box. So this works OK for development.

However, for deploying packages to production, I can't see this would work well. So if you were deploying some package to production, wouldn't you have to open dev studio and open each package and then change the data source? Doing so actually changes the dtsx file. It's never a good idea to change your source code (i.e. dtsx file) when deploying to production. As a workaround, I suppose you could control the connection manager's connection string with a config file in production only, but then you're not testing those config files during development. Any other thoughts?

If you’ve regularly found yourself changing the targetserver property and the data source connection string before you deploy yourcube to another environment, it’s simply because you’re not aware of a ...