Comparing Database Versions

This post was inspired by a recent thread on the MSDN SSIS Forums. Here’s the scenario:
Let’s say that you have a source database and a destination database. Let’s take this one step further and say that you have multiple instances of these source and destination databases deployed in your environment. Let’s make things a little more complicated by saying that there can be (and probably are) different versions of these databases deployed, and that you track the current version in a database table. To make matters worse, it’s possible that a corresponding source/destination pair might be updated at different times, so their versions might not always match.
This sounds like a pain in the butt, but also sounds like a pretty common scenario too. So how do you ensure that the SSIS package that you’re developing will only move data from source to destination if their versions match? With SSIS variables and Expressions, the solutions is very simple. Here’s what’s needed:
  1. Two connection managers, one for the source database and one for the destination database.
  2. Two package variables, one to store the version number from the source database and one to store the version number from the destination database.
  3. Two Execute SQL tasks, one to retrieve the version number from the source database and one to retrieve the version number from the destination database and store them in the variables.
  4. A sequence container around the two Execute SQL tasks. This is technically optional, but it makes it easier to build the package logic if you set things up this way.
  5. One precedence constraint configured with a Success constraint and an SSIS Expression instead of just using a Success/Failure/Completion constraint.
  6. Optionally, a second precedence constraint set up with a completion constraint logically opposite Expression, so you can have a second path of execution for when the versions do not match.
  7. The rest of your package logic. This is up to you, I’m afraid.

When you’re done, you’ll have something that looks a lot like this:

Enjoy!

Advertisements

About ssimagine

My name is Matthew Roche, and I am a Senior Program Manager with the SQL Server product group at Microsoft. I work on Master Data Services and Data Quality Services, and have previously worked on SQL Server Integration Services. Although I work for Microsoft and will be posting on technical topics, I want to stress that this is a personal blog, and any opinions posted here are mine and mine alone. I built my career around SQL Server and Microsoft technologies for well over a decade before I joined Microsoft as an employee, and I plan on using this blog to share my personal experience and opinions. They may well be shaped by my experience on the SQL Server team, but they’re still mine, and not that of Microsoft, disclaimer, disclaimer, etc., etc..
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s