Package configurations in SQL Server Integration Services (SSIS) gives you a powerful tool to manage configuration data (server names, ip addresses, file shares, resource locations) and make them portable from environment to environment with small amounts of changes. Although there are several package configuration options in the SQL Server 2005/2008 arsenal, one of the best is creating a configurations database. This gives you the ability to...
<ul>
	<li>Include configuration data in your normal SQL backup routine by backing up the configuration database.</li>
	<li>More familiar for DBAs and Programmers.  Use TSQL statements to update large amounts of configuration data quickly.</li>
	<li>Manage multiple configuration scenarios across production, test and development environments by having local configuration databases on each allowing you to seamlessly move packages from environment to environment.</li>
</ul>
Now that the benefits have been laid out and the overall benefits of having completely mobile platform to de<!--more-->velop and deploy SSIS packages on are apparent.  We can take a look at what we will need to do to set up packages to utilize this layout.  Configuration of a package can be completed in three steps.
<ol>
	<li>Create a Environment Variable on the datbase server to reference the location of the SSIS Configuration database.</li>
	<li>Create the SSIS Configuration Database.</li>
	<li>Build/Modify packages to utilize this new configuration data.</li>
</ol>
<strong>Step 1:  Creating a Environment Variable on the database server to reference the location of the SSIS Configuration database.</strong>

To achieve this we must have planned out what we will call the configuration database in step 2.  For this example we will use "SSISConfig" as the database to house our configuration data.  In order to create the environment variable we will use the built in Windows Control Panel System Manager.  Go to Control Panel -&gt; System -&gt; Advanced -&gt; Environment Variables.  Select Create New.  In the name field use SSIS_CONFIG and in the value use

"Data Source=localhost;Initial Catalog=SSISConfig;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

save the environment variable then exit out of control panel.  This has set up a Environment Variable we can use to point to the location of our config database within our SSIS packages.

<strong>Step 2:  Create the SSIS Configuration Database.</strong>

Using SQL Management Studio.  Create an new database.  For this example we called it SSISConfig.  Also, if no other databases with test data exist on the server, create a test database (no data necessary) to connect to.  For this example we will call it SSISTestData.

<strong>Step 3:  Build/Modify packages to utilize this new configuration data.</strong>

Now comes the fun part.
<ol>
	<li>Open up BIDS (Business Intelligence Design Studio) and create a new project (SSISTest)</li>
	<li>Add a connection manager to the default package for the SSISConfig database.  Do this by right clicking in the Control View and selecting New Connection, Add, and specifying the SSISConfig database.</li>
	<li>Repeat Step 2 for the SSISTestData database connection.</li>
	<li>Go over to the Package Explorer tab and make sure they appear under Connection Managers.  If not you most likely did not select them after creating them in steps 2 and 3.  Go back and make sure they are highlighted in the list and select "Add".</li>
	<li>Back in Control Flow Panel.  Right Click and select Package Configuration</li>
	<li>Check off "Enable Congfiguration"</li>
	<li>Add a new "Environment Variable" package configuration and specify the SSIS_CONFIG Environment Variable.</li>
	<li>On the next screen target the connection string of SSISConfig Connection as the target of the package configuration.</li>
	<li>Save that configuration and Add a new "SQL configuration".</li>
	<li>Specify the SSISConfig as the connection.</li>
	<li>Select New next to the configuration table and use the default options (or make changes as necessary if you like)</li>
	<li>Select the configuration filter to be the SSISTestData connection</li>
</ol>
You now have a database configured connection string where the connections database is specified in an environment variable on the machine.  This makes the package configurations and the configuration database portable and easy to manage from any server location.

Good luck.  Leave comments with any questions.