MSSQLTips

This post is related to this tip: Using SQL Server Package Configuration with SQL Server Integration Services (SSIS) Packages

http://www.mssqltips.com/tip.asp?tip=1405

It was a great article.  How do I deploy to a test server though?  Do I need to create the SSISConfig database in the test server?  What do I need to do to get the values into the test server?  Sorry, but I am very new to this.  I saw examples for deploying using XML files, but not for SQL Server package. 

You would generally want a separate configuration database for environment - development, test/QA, and production.  The reason is that your SSIS packages will need different configuration values in each environment, particularly database connection strings.

Let's assume that you start by creating a configuration database in your development environment.  Create a new SSIS package and start adding SQL Server package configurations.  The values will be saved to the SQL Server table that you specify when you define each package configuration.  When you are ready to setup your test environment you want to copy the SQL Server table from development to test.  I open the table in SQL Server Management Studio and copy/paste the rows into an Excel spreadsheet.  Then script out the table, create it in the database on the test server, then copy/paste the values from the Excel spreadsheet into the table, again opening the table in SQL Server Management Studio.  You could use bcp to copy the table or even write an SSIS package to do it.

 Thanks. I guess it is a bit manual and this was exactly what I was looking for. I will test it tomorrow.

looks great but i was wondering if you have any artical about xml file configuration which has all the variables so the parent and child packages can get the values from .dtsconfig file.

Thanks

Derek

There has not been a tip published on the XML config files yet.

This is how to do XML config:

http://www.sqlis.com/26.aspx

There is also a tip on using XML package configuration on MSSQLTips here: http://www.mssqltips.com/tip.asp?tip=1434