Using one package with multiple configurations in SSIS

Question

I know this question has been asked before and I've been doing some research on using a single package with multiple configurations.

An example of this might be: A package that has an FTP control in it.

We might have two or three different FTP sites to connect to, but we only need one package. What I'm looking for is to run that package with three different configurations.

Example configurations:
- Username
- Password

After doing some research on the use of a configuration table, I'm still confused as to whether or not the standard configuration table would work with this scenario or not in SSIS (2008 R2). The article I found that I'm confused by is:

SSIS TABLE DRIVEN PACKAGE CONFIGURATIONS WITH ROW LEVEL FILTERING

If I read the article, some of it makes sense but I'm still not entirely certain that I would need a custom solution to do this or not.

What I'm after is this: I am designing a three tier architecture (three layers of packages).

The first layer will always do file acquisition, file preparation, and translations to a common XML format based on a provider's proprietary format, whatever it may be.

The second layer would take the common format and transform that into a "destination format" and then the third layer would take the destination XML format and insert it into the database.

My thoughts are to possibly use a table to configure the second and third layers and have the top layer using an XML configuration file since it will change based on the provider. Any thoughts on this would be appreciated.

Answer 1

I highly recomend this book and the package configuration framework contained therein:

Microsoft SQL Server 2008 Integration Services: Problem, Design, Solution

ISBN: 978-0-470-52576-0

For me, this book took all the mystery out of package configuration with easy to understand step by step set up and examples. It presents a robust beginning to end solution that I was able to implement in less than a day. Code download link here.

The authors do an excellent job of walking you through package configuration theory and practical setup as part of their larger package management framework.

I would like to second their recommendation to make a "Template Package" from which you build all other packages. The template has all the SSIS PDS goodies already in it so you only need build those once.

One of the most useful features is their approach to package configuration which stores all your configurations in one table in sql server and shows you have to access these at the system, application, and package levels for a layered configuration approach that I find most useful for situations like the one I think you are describing.

Setting up a general configuration as part of the template package once and then tweaking it from there as needed has saved me hundreds of hours in development time.

Not to gush on about this solution, but I found it particularly useful for Username and Password configurations as these are two of the core examples in the book. It can be a bit tricky to simultaneously configure the package with a Username and Password while simultaneously giving these two key pieces of data an adequate level of security.

For example: If you are interested in keeping Username and password secure, you would almost never want to just store these in package scoped variables. This book shows you how to add Username and password as configurations, keep them secure, and how to easily keep them up to date.

Although hopefully when your packages run in production (at least) you are using a Service account where the password never expires (all the more reason to keep those credentials secret and safe) and not your own user credentials.