SSIS Connections, how to parameterize the connection

I'm trying to figure out a way to "parameterize" the data connection for SSIS packages. So I'm working with config files (and also sql server config tables).

Say I have an Oracle connection, and I'm testing a package in test d.b. And another developer has a different package using the same oracle connection. He needs to pull from Prod but I want to pull from test.

How is this accomplished ? I'm new to SSIS and have come to understand config files (and sql server config tables), but now I'm perplexed  . . . because I came to the conclusion that for an Oracle connection, I would have a config file in a common location, like
E:\SSISConfigFiles\EDW\Oracle_EDW.

And my connection is called Oracle_EDW, to keep it generic.

But if we both point to this config file, then we can't pull from diff d.b.'s, one from Prod and one from Test?

So how does one accomplish parameterizing a connection in SSIS ?
LVL 1
Alaska CowboyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<not an answer>  Which version of SQL/SSIS are we talking, 2008 or 2012?  Big difference.
0
Alaska CowboyAuthor Commented:
2008 R2, but soon we'll be moving to 2012.
0
Bob LearnedCommented:
I believe that you are looking for a package configuration, that is described here:

Understanding Integration Services Package Configurations
http://msdn.microsoft.com/en-us/library/cc895212.aspx
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Alaska CowboyAuthor Commented:
TheLearnedOne, thank you for the link. I have a basic / good / recently acquired understanding of package configurations. I've used them successfully in learning SSIS (and BIxPress).

In thinking about actual implementation, I'm trying to understand how configurations are set up globally vs. a package.

so if I have an Oracle connection using a config file, do I use a common connection / file ? such as E:\ConfigFiles\Oracle\Oracle_my_system.dtsConfig.

If this file was on the test server and everyone used it, then there's only one d.b. to point to - the one in the config file. But what if someone needed to pull from Prod - if you change this file, then everyone using this connection would be pointing to Prod !

So then I'm thinking, I guess we would need a separate config file for oracle every package. or somehow parameterize this and so a parameter would be the path of the config file ? or something like that. So that different packages can point to different Oracle d.b.'s.

So that's my question, hope this makes sense.
0
Bob LearnedCommented:
If you want global settings, then you can make global settings, and then you can define project-level settings.  Then, you can decide with settings that you want to use.
0
Alaska CowboyAuthor Commented:
Ok, that makes sense, but how do you define project level settings ?
0
Bob LearnedCommented:
Here are some options that you might think about:

http://stackoverflow.com/questions/2470230/ssis-global-variable

SSIS has variables that can be global to a package, but to span multiple packages, I can think of the following options

Passing Variables

Have Main Package define a variable and pass the value as a parameter to all packages that it calls. Call the variable the same name in all packages for easy identification.

Config File

Use the same SSIS configuration file across packages and store the value in there.

Environment Variable

Use a windows environment variable that is read from other packages

Registry Value

Store in Windows registry and read for each package - make sure you store under a tree that all packages can see otherwise you may run into permissions issues. Eg HKLM

Database Lookup

Store the value a table structure.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alaska CowboyAuthor Commented:
Ok, excellent, thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.