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

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 ?
Alaska CowboyAsked:
Who is Participating?
Bob LearnedConnect With a Mentor Commented:
Here are some options that you might think about:

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.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<not an answer>  Which version of SQL/SSIS are we talking, 2008 or 2012?  Big difference.
Alaska CowboyAuthor Commented:
2008 R2, but soon we'll be moving to 2012.
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Bob LearnedConnect With a Mentor Commented:
I believe that you are looking for a package configuration, that is described here:

Understanding Integration Services Package Configurations
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.
Bob LearnedConnect With a Mentor Commented:
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.
Alaska CowboyAuthor Commented:
Ok, that makes sense, but how do you define project level settings ?
Alaska CowboyAuthor Commented:
Ok, excellent, thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.