Quickly changing SSIS-packages data source parameters for easy migration

4
votes

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!

1
sql-serverssisodbcbids
Have you tried using expression for the database connection string? It can be set through variables. – Gowdhaman008
Changing the database connection string at least works when the source type stays the same. When switching over to production I will also be switching from an OLE data source to an ODBC data source. I have no idea if changing the connection string will be enough then. I don't really know in what ways different source types differ. – Muuse
Do you need to dynamically change the Provider for a connection manager? What is your Provider type in Test and what do you need it to be in Production? Examples could be (Test) "Native OLE\DB SQL Server Native Client 10.0" and (Prod) ".Net\Providers\Odbc Data Provider" – Jon Jaussi

1 Answers

2
votes

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...

Choosing a configuration level

Configure your variable value

Use your variable for your Connection Manager's ConnectionString

Store your variable value in a Configurations table

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.