Everyone who has developed SSIS or DTS packages has run into the issue of migrating them from one server environment to another.  Whether this be a testing to production deployment or a server upgrade it has always been a challenge to make the necessary changes.  When you are considering environments of 100+ packages maintain all of them can be a very tedious task if not impossible for IT staff to ensure 100% accuracy.  This has lead to many home brewed solutions to try and make this process less painful.

Thanks to the SQL 2005 and XML configuration this task has been made simple and pain free. 
<ol>
	<li>Create a new Integrations Services Project in Visual Studio.</li>
	<li>Create and Select the Data Flow Task</li>
	<li>Create and Configure your connection object (OLE, Excel, etc...)</li>
	<li>Select "SSIS" &gt; "Package Configurations"</li>
	<li>Check "Enable Package Configurations" in the dialog box.</li>
	<li>Click "Add".</li>
	<li>Browse to the location of your XML Configuration file and add or create a new one by typing a file name. (screen1)</li>
	<li>Select "Next".</li>
	<li>Browse to your connection object in the tree.</li>
	<li>Check the connection string option (screen 2).</li>
	<li>Specify the connection string.</li>
	<li>Click "Next".</li>
	<li>Click "Finish".</li>
</ol>
<img class="alignleft size-full wp-image-95" title="dtsconfigscreen11" src="http://egroupus.files.wordpress.com/2009/01/dtsconfigscreen11.jpg" alt="dtsconfigscreen11" width="493" height="442" />

 

 

 

 

 

 

 

 

 

 

 

 

<img class="alignleft size-full wp-image-96" title="dtsconfigscreen2" src="http://egroupus.files.wordpress.com/2009/01/dtsconfigscreen2.jpg" alt="dtsconfigscreen2" width="493" height="442" />

 

 

 

 

 

 

 

 

 

 

 

 

The connection strings for your package are now contained in the XML file you created.  You can use a single file to house all of the connection strings for your SSIS packages.  When you are ready to move to another server.  Just change the XML file and all of your packages will be updated with the new connection information.

You can also use configuration files in the same manner for query variables by referencing a question mark in your query and inserting a variable name.

<address>Jason Webster, eGroup </address><address><a href="http://www.eGroup-us.com">http://www.eGroup-us.com</a></address>