Configurations in SSIS are a lovely tool that when implemented
correctly can greatly reduce package maintenance. It is very possible
to over configure a package, so be sure that you only do what you need
to in order to make life easier. Sometimes too much configuration can
equal confusion. So the big questions, what is a configuration, when
should I use it and how do I use it.
Let’s tackle that first one then roll right into the other two. What
is a configuration? In simplest terms a configuration is a way to
modify a property without opening a package. This can apply to almost
everything inside your packages. In this post we are going to focus out
attention on connection managers because that is where configurations
are most commonly used. Think of the properties of a connection
manager. There is a name, a connection string, an initial catalog,
server name, username, password and some other things as well. A
configuration can be put on one or multiple properties. So you can
configure the server name, which is a very common property to change, or
the username and password. That gets part 1 out of the way (the
property part) now for part 2, the modification without opening the
package part. A configuration is going to be stored someplace outside
the code that makes up the SSIS package. This may be in an environment
variable on a server, or more commonly in either an XML file on the file
system or in a SQL Server table. When you make a change in the
external location the next time the package is run it will use the new
value that you entered.
Now that we know what a configuration is (a way to update a package
property from outside the package) let’s chat about when to use them.
In general use a configuration whenever you want to be able to change a
value stored inside your package without actually having to open the
package. Back to our connection manager example. The most common place
to put a configuration is on the connection string (or just the server
name). The reason for this: as you migrate the package from DEV to QA
and ultimately production you have to change the server where the data
is being pulled from. A package running in production using DEV data is
absolutely useless. So, rather than having to open all your packages
and change the value of the server name (from ServerDEV to ServerPROD
for instance) you can put a configuration on the server name property
and change the value inside the configuration file. Which brings us to
part 2 of when you use configuration files: when you have a common value
among multiple packages. 50 packages for the same application are
going to probably all hit the same set of databases on the same
servers. It is unlikely that all 50 will hit 50 different servers. So
you will have a connection to the AdventureWorks database in all those
packages. Just use the same configuration file to change the server
name property on the connection in all your packages and now you only
have to change one config file rather than open and change 50
connections. Boom! Time saver!
Configs are not limited to only connection managers. A configuration
can be applied to things like the value of a variable, or the
executable on an execute process task, all the way down to the sql
statement that will be run in an execute SQL task. We are just using
connection managers for this example because they are the most commonly
Finally the reason you are reading this blog, I think… HOW?!?!?!?!
The how portion is a little more complicated than “when you want to
change a value”. The process for adding a configuration is the same
basic process for all 5 types on configs. The manner in which they are
used (ie stored, accessed, maintained, etc) however differs slightly.
Step 1: Enable Configurations
No, configurations are not enabled by default. What were they
thinking? Why aren’t they enabled? Why do I have to enable them? Stop
complaining for one moment, it’s only a check box and it is right next
to the place where you go to add them anyway. You’ll already be on the
screen, no big deal. Begin by right-clicking in a blank spot on the
Control Flow design pane or click SSIS from the menu at the top of
BIDS. Either way, select Package Configurations from the list of
Next enable configurations if they are not already enabled by
checking the wildly annoying (just kidding, it’s not annoying and
actually can be quite handy) check box in the top left labeled Enable
The screen lights up because it is happy to be used and you can click Add at the bottom of the screen to get started.
Step 2: Choose Your Own Configuration
Ok, so a play on words with the old kids books Choose Your Own
Adventure didn’t work out as well as I planned in the title of a step,
but you get the idea. A fun drop down menu will allow you to choose the
type of configuration you want to use. Let me just quickly give you a
rundown of what each one does. Please note how nicely the list is
composed alphabetically so you can quickly find the type you want. Oh
wait, it’s not in alphabetical order? No. What order is it in? umm….
If you have a good answer for this, please let me know, I have always
wondered why they chose the order they did.
Step 3: Location or Name
Once you chose the type of configuration you need to pick out where
that configuration will reside (if it is XML or SQL Server) or the name
of the source (Name of the registry entry, name of the environment
variable, name of the parent variable).
In the case of XML browse to a location on the file system and name
your file. If you are doing this for a connection manager I recommend
using the name of the connection manager to be the name of the file that
way you can easily identify what it is used for when browsing the file
system. In our example we are configuring the Adventure Works
connection that in our package is called OLEDB_ADVENTUREWORKS, so we
will name the file OLEDB_AdventureWorks.dtsconfig. If you have a
pre-existing configuration browse to it on the file system and select
If going the route of SQL Server you will need a dedicated connection
in your package to your configuration database. I have one called
OLEDB_SSISCONFIGURATION which connects to my SSISConfiguration
database. On the SQL Server settings choose the appropriate connection
and then select the table that stores all your configs.
If you don’t already have a configuration table set up you can use
the nifty little button that says New next to Configuration table to
create one. It will even automatically script the create table for
you! How nice. At any rate, select the table that you store the
configs in and then continue reading below this image…
At this point we are just about where you would be with an XML
configuration. Where you normally name a configuration with XML or pick
an existing one, you do the same thing with the configuration filter
box. If there are no existing configurations in the table or you want
to add a new one type in the box. If you want an existing one, choose
it from the drop down menu. Either way think of the configuration
filter like you think of the name of an XML configuration file.
For an environment variable pick the entry from the drop down that
hold your value you want to use. For registry entry type in the name of
the entry and for parent package variable type the name of the variable
in the parent package.
From this point things are the same, the only difference is the number of properties you can configure.
Step 4: Pick Your Property (Or Properties)
Our little scenario depicted a world where we were configuring an
AdventreWorks connection. When you click next from the previous
screenshots the image below will be what you see. A list of all the
properties in your package that you can configure. It’s like a
configuration buffet. For XML or SQL Server you can check off as many
items as you would like. Be aware though, that if you check off an item
and want to use this configuration in another package, ALL checked
properties need to exist. By that I mean, if you check the option for
Server Name on the connection called OLEDB_ADVENTUREWORKS and the
connection OLEDB_ADVENTUREWORKS doesn’t exist, the package will throw an
error. This is not so much a problem if you go with a single
configuration per connection, but if you start to share configuration
files with multiple connections or multiple object properties it can
become an issue.
That problem doesn’t arise with Registry, environment variable or
parent package variable. The next image is what you will see there.
Notice that the screen is the same except it is missing the check
boxes. Here you simply highlight the property to configure. Since
these types of configurations can only hold one value, you can only
configure one value.
Make your selections and click Next.
Step 5: Name that Config
The final screen will allow you to put a name on your configuration.
This name means nothing at all, that’s right, nothing, except for it
helps you know what you put in the configuration. Again, my
recommendation is to make it the same as what you configured, for
instance the connection manager name, or Package Variables, or something
like that. Now when I look I know that this config entry is for my
Adventure Works connection.
One nice feature is that you can add multiple configurations to your
package. Let’s say you do have a SQL Server configuration, there is a
connection to that database. So naturally you need to change the
database it looks at. What do you do? Add a configuration to tell the
connection to your configuration database where to look. Read that like
3 more times to make sure you understand it before reading further.
Please, this is not a joke, it is a very important concept. In this
case, as you can imagine you will not use a SQL Server configuration.
The most common things to do here are use a single XML file or an
environment variable. I usually set up an environment variable that
holds just the connection string to the configuration database. That
environment variable then goes on ALL servers where the package will run
and is named the EXACT SAME THING!
Notice in the screenshot below that I used an environment variable
called SSISConiguration to configure the ServerName (that gets a little
cut off on the far right) or the object OLEDB_SSISCONFIGURATION (also a
little cut off). This will tell that connection to point to the right
environment. In DEV that connection string will hold the dev server
name and credentials. In QA it will hold the configuration database QA
server name and credentials, and same for PROD, but with prod server
name and credentials. I also highlighted the arrows on the right side.
You can move configurations up and down in the list. This is VERY
important because configurations are applied in the order they show in
the list. As it stands our package that we just moved to QA will use go
to SQL Server (still pointing to DEV) and configure the value of the
Adventure Works connection. Then it will tell the connection to the
configuration database to point to QA. Those events happened backward
and we got bad data. We need to point to QA configuration database then
pull all values out of the configuration database. Use the up arrow
with the proper configuration highlighted to move it to the top of the
Everything should look like this now:
That about wraps up configurations. Just a couple of wrap up items:
I hope this has helped someone out there with their configuration
issues. If you want specifics on how to set up the individual
configuration types please let me know. I’d be more than happy to do a
blog with details on how to specifically set type up rather than a
summary like this. It would be detailed, short, sweet and to the point.
I did a webinar on this same topic for Pragmatic Works Free Training
on the T’s on June 7, 2011. If you are interested in seeing that so you
can follow along with some demos you can catch it by visiting this blog
entry for as long as we keep our webinars on the website. I don’t know
how long we have access to those, but as of this blog it is definitely
still working. Here is the link SSIS Webinar Recording Is Up.
Hey Brad, I am not getting the pictures.
Sorry about that. I had copied the post off my website and pasted it in here and had the images imbeded into the post over there. Should be working now. Thanks for the heads up. Let me know if there are any other problems you see!
Looks good Brad. Nice post by the way.