SSIS packages are great ETL tools and can do just about anything you need in
terms of ETL. Most organizations start out creating SSIS package one by one
until they have dozens, hundreds, or even thousands of packages. I have worked
with one client that ran over 4,000 packages. This can be a nightmare to
maintain. You can save yourself a lot of work by deciding upfront how to
configure your packages using configuration files or tables. We are going to
discuss configuration tables in this article.
We are going to look at a simple example of passing information to a package
with a configuration table. Then we will go over using configuration tables on
multiple packages. Imagine running dozens of packages that point to a server and
the server name changes. If you have a configuration table that is feeding this
server name to every package you can make a single change to the configuration
table and all the packages are updated. This can reduce your maintenance time
We are going to build a couple of simple packages with connections. These
packages will connect to the adventure works database which is freely available
on codeplex. Since you will not being pulling any data you can use any database
you would like.
1. Drag in an Execute SQL Task into a blank SSIS package.
2. Double click on the Execute SQL Task to edit it.
3. Click on the connection drop down menu and select New Connection.
4. Click New.
5. Enter in your server name and the adventure works database in the
6. Click ok twice to get back to the Execute SQL Task.
7. Enter “Select 1” as the SQL statement
(This query will not pull any data. It is just used to test the
8. Click ok and debug the package. The Execute SQL task should turn Green
Now you will create a configuration table to feed the value of the connection
to the package.
9. Stop the package from debugging.
10. Right click in the connection manager and select new OLEDB connection
11. Create a connection to a blank database. In this example we will use a
database called Config.
12. Right click in the control flow and select Package Configurations
13. Place a check next to Enable Package Configurations.
14. Click add; (Click next if the welcome screen appears.)
15. Select SQL Server in the Configuration Type drop down menu.
16. Set the connection to the Config Database
17. Click the New button next to the Configuration Table Dropdown menu.
18. Click ok. This will create a table in the Adventure Works database.
19. In the configuration filter type Development.
20. Click next
21. Place a check next to the connection string property of the adventure
22. Click Next, Finish, and Close.
23. Debug the package again, the Execute SQL Task will still turn Green
The Execute SQL task is not using the connection saved in the connection
manager. It is using the value that was saved in the config database on the SSIS
Configurations table. You do not have to leave the name of this table. In fact
most businesses have a practice of no spaces in table names. So you could have
created the table name SSISConfig or any name you prefer. You can test where the
package is getting the adventure works connection string by changing the
connection on the package to a database that does not exist.
24. Double click on the adventure works connection in the connection
25. Change the database name to adventureworks1
26. Debug the package and you should see the Execute SQL task turn Green.
(There is a blue dot next to the Adventureworks2008 connection indicating the
connection is fed from the configuration manger, this is a feature of because BI
27. Open SQL Server Management Studio and view the values in the table.
28. Return to the SSIS package and make a copy of the package in the project.
Click on the package in the Solution explorer and pres Ctrl+C then Ctrl+V. A
copy of the package will appear below it in the project.
29. Double Click on the package
30. Debug this package.
Notice this new package runs successfully. It is using the same configuration
table the first package is using. If you make a change to the table both
packages will be updated. This is a major time saver in maintaining the packages
in the future. Imagine having one hundred packages that have a single change
like the name of a server or database name. Updating these packages becomes a
one minute task instead of a one hour task. A proper configuration setup also
saves you from having to redeploy the packages.
I need some help with configuration tables. My requirement is some thing like this.
I am looking for a way to connect to any environment to pull data. For example, I am working on a breakfix and I dont have the data available for my test case in DEV or QA.
I would like my SSIS package to read source data from PROD and process it into DEV or QA. how to achieve this using configuraion tables.
By the way we are using a different ETL tool and planning to migrate to SSIS. And this is quite possible with system configurations in the current ETL tool we were using.
I am trying to find that something similar is possible with SSIS. Really appreciate some help on this.