Quickly changing SSIS-packages data source parameters for easy migration

Question

I would need to migrate a SQL database from Sybase to MS SQL Server. Before doing the actual migration on the production server I first created an SSIS-package with SQL Server Management Studio's Import/Export Wizard for testing with other databases. The test migration was successful and I would now like to deploy my SSIS-package to the real servers.

However, it seems I cannot simply run the package in Management Studio choosing different data sources for it - it only runs on the same databases for which it was created. Now, it can be edited in something called SQL Server Business Intelligence Development Studio (or BIDS for short)(I am using the SQL Server 2008 version), but going through every data flow task changing the destination source manually for each of the ~ 150 tables I am moving is ineffective and also introduces a possibility for error.

I there a way to quickly change what data source is to be used for ALL destination sources in ALL the flow tasks of an SSIS-package? If not, what simple method is there for testing migration with test databases first and simply changing the data sources when deploying?

I am using ODBC data sources, but for some the package shows OLE-sources in BIDS instead.

I hope I was clear enough. If you have additional questions, please ask! Thank you!

Solution

I would use a variable for the ConnectionString property of the connection manager. A package level configuration can be very useful for accomplishing this task. Several ways to do this. I prefer to use a table in SQL Server that holds all the configurations for all packages. This can be especially effective if you have multiple packages and need to dynamically change a set of connection managers across those multiple packages.

The basic steps are:

1. Opposite click on your SSIS design surface and select "Package Configurations..."
2. Create a package level configuration of Configuration Type "SQL Server"
3. Store your connection in a Configuration table in SQL Server
4. Alter your Connection Manager to use a variable for the ConnectionString Property
5. Populate that variable from the Configuration table via your package level configuration
6. When it comes time to switch from Test to Production, simply update the connection string in your configuration table

These screenshots can help...

This is part of a larger package management framework that I implemented using this book:

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

I highly recommend it. Should take less than a day to set it up. Book has step by step instructions.

This question and its associated answers also helpful.

Comments

[Muuse]

I noticed that when I created a new SSIS-package with the wizard, it now showed as an ODBC source in BIDS just like I wanted. In a similar way you and @Gowdhaman008 suggested I change the configurations through a variable, I was able to simply test the package by right-clicking the source in the Connection Managers-window and choosing Edit. For some reason an OLE source cannot be changed to an ODBC source but vice versa goes. I'll accept your answer. Even though it's not what i did, it helped me along the way and probably works for anyone finding this in the future. (and it was well made too!)

[Jon Jaussi]

Best of luck and thanks for posting what you actually did to answer your question. Too often folks fail to do that and it leaves the circle incomplete.