This articles is for SSIS beginners who are new to SSIS and would like to understand what is SSIS Configuration and why is it important. SSIS Configuration is often mentioned in SSIS books but rarely anyone explains the basics so those very new to SSIS may find it confusing and not so easy to follow.
In this articles I will focus on explaining the basics and provide links to different ways of implementing SSIS Configuration for SSIS 2005, 2008 and 2008 R2 which is the same and new Configuration approach in SSIS 2012.
What is SSIS Configuration?
Let's start with basics and answer the question What is SSIS Configuration? In short SSIS Configuration is referred to an approach that allows an SSIS Package to work properly on different environments.
Now long answer... Let's start with the process of building an SSIS Package.
A package is generally developed on a local PC/Laptop (with version control like TFS) or sometimes on Development Machine.
In the below image we have someone developing a package. I called it Local Environment as it is being developed on a local PC/Laptop
Task: We have a set of files we want to import and all of them exist in a folder and on local environment we placed them in C:\Data\
Below picture describes the situation.
Is there a problem in providing static value like C:\Data\? Well it would work in very simple situations for instance when we develop a one-off package and execute it only once on one machine but in most project SSIS needs to be developed locally, moved and executed to development environment (dev testing), moved and execute on Test Environment (UAT), and moved and executed regularly on Production Environment.
NOTE: Number of environments may be higher or lower.
Below is picture that shows a package and File Path ? ? ? If we have 4 environments = potentially 4 machines then problem appears when files are not kept in the same location (see below paths).
If package was developed locally and we provided a value C:\Data\ than if we need to execute the package on different environment than potentially the files are located somewhere else and we need to change the location. We could modify the path manually and execute on different environment but that is very risky as we modify package content (error prone / accidental changes / against standards/best practice) and this would be time consuming
Another solution would be to ensure that each environment has exactly the same paths and names but that is very difficult to achieve so it is not really practical (but do ensure to make environment as similar as possible).
SSIS Configuration Options
So how SSIS handles it? There are various methods to achieve that. In SSIS 2005, 2008 and 2008 R2 several methods have been used which rather created confusion and inconsistent standards however SSIS 2012 introduced new method that hopefully will overcome all previous build-in limitations and make "old methods" redundant, but we will have to wait and see if new method can really replace all of them.
In the near future I hope to describe both methods in more details... I added it to the "wish list" page.
SSIS 2005, 2008 and 2008 R2 - uses Package Configuration (different variations) and custom component.
SSIS 2012 - Project Configurations - In this article I give an overview of using SSIS 2012 Parameters as a way to implement SSIS Configuration.
I hope this simple article gives you good overview of the configuration challenge and new articles I will provide