Friday, July 13, 2012

Upgrading BIDS Projects to SQL Server Data Tools 2012

Upgrading your Visual Studio 2008/BIDS 2008 R2 projects and solutions​ to the new VS 2010/SSDT format isn't exactly fraught with peril but it isn't a walk in the park either. SSAS and SSRS projects convert seamlessly since those two products didn't change but upgrading those SSIS packages may take a little bit of work.

Before You Upgrade

The following steps are critical to making sure you can back out of an upgrade if something goes completely wrong. Remember, source control is your friend (even if you still stuck in Visual SourceSafe, just sayin'). We use a combination of VSS and Team Foundation Server right now so you might need to translate the Source Code Control terminology to your platform of choice.
  1. Make sure you have the current versions of any third-party SSIS components you're using.
  2. Make sure everything in the project is checked into source control; you don't want people monkeying around with the projects you're trying to upgrade.
  3. Apply a label or tag to the current version of the project in source control. I use "Pre-2010/2012 Upgrade".
  4. Check out the entire project (recursive) making sure to get the latest version. If it makes you feel better, you can do a "Get Latest" and then check out the whole thing.

Conversion/Upgrade Wizards

The Visual Studio Conversion Wizard is pretty straightforward. It has a screen that gives you an idea of what it's going to do. One thing you need to watch out for is that it tells you that it is going to check out the project or solution if it is under source control. What isn't exactly clear is that it only checks out the solution and project files. If you don't check out everything in your solution, the subsequent SSIS Package Upgrade Wizard is going to choke on a bunch of read-only SSIS packages, hence the "check out the entire project" step above.

Once the Visual Studio Conversion Wizard does it's thing, it kicks off the SSIS Package Upgrade Wizard. This is where the real work gets done. The first step is pretty useless, just read the description of what it's about to do, check the box that says "Do not show this starting page again", then click "Next".

The next step is where you choose which packages you want to upgrade. You want to upgrade all of them so make sure they are all checked off and click the "Next" button.

The "Select Package Management Options" step actually requires a slight change from the default options. The "Validate upgraded packages" option should be selected while the other options should remain at the default. Do not check the "Create new package ID" option. If your selections look the same as those in the fuzzy screen shot below, you're ready to click the "Next" button.


You should also un-check the "Ignore configurations" option (unlike my screenshot below). That will tell the upgrade wizard to correct the connection strings to the current version of the SQL Server Native Client. The screen shot below is incorrect in regard to this option.

Select Package Management Options dialog

You should now be on the "Complete the Wizard" screen. If you're feeling brave, click the "Finish" button. You'll have to do it eventually whether you're from Finland or not. (Besides, people from Finland are Finnish, two Ns.)

Once the wizard has had its way with your packages, you'll see a screen that looks somewhat like the next fuzzy screenshot. Yes, you'll probably even have some of those red things too; you didn't expect everything to be easy did you?

Responding to Murphy

Without a doubt, there was someone named Murphy working on the SSIS Package Upgrade Wizard. Don't let it get you down though, nothing too terrible should happen during the upgrade. If you click on those "Messages..." links the wizard will tell you what went wrong, usually in great detail. Compare those errors with the list below and see if you can find a solution to your troubles. If the answer isn't there, please post a comment, especially if you resolve the problem!

Oracle Source is not Compatible

Whoever changed the GUID for the Microsoft Connector for Oracle by Attunity component should be shot (maybe just a flesh wound). It's not terribly hard to fix but darn inconvenient.

The following two errors may appear together.
Error: The version of Oracle Source is not compatible with this version of the DataFlow.
Error: Cannot find the "CurrentVersion" value for component {4CAC6073-BCA7-430E-BD29-68A9F0012C6D}.
Also, When you open the package in SSDT, the Oracle Source component will appear as shown below instead of having the proper icon.

Fix: Change the componentClassID in the XML code of the offending Data Flow component from "{4CAC6073-BCA7-430E-BD29-68A9F0012C6D}" to "{CB67CD40-126C-4280-912D-2A625DFAFB66}". Upon reopening the package the Data Flow should look like this:

Script Compilation

Error: Failed to compiled scripts contained in the package. Open the package in SSIS Designer and resolve the compilation errors. (Several other script-related errors may follow.)

Fix: Do what it says, "Open the package in SSIS Designer and resolve the compilation errors." :)

Wrong Component Versions Installed

The following are examples of what might occur if you don't have the correct versions of one or more of your third-party components installed.


Error: The connection type "MSORA" specified for connection manager "<Connection Manager Name>" is not recognized as a valid connection manager type.

Fix: Make sure you have both 32- and 64-bit (or just 32-bit if you're still running on a 32-bit OS) packages of the version 2.0 Attunity driver installed. Also note that installing 2.0 may not remove earlier versions.

Error: The "TF Upsert Destination" failed to cache the component metadata object and returned error code 0x80131600. (May mention a different Task Factory or Xtract IS component but the resolution is the same.)

Fix: Install the latest version of Task Factory (32- and 64-bit)

Wrong Data Provider in Config File

This last one is just a warning but it will be pretty common until you update the configuration files or, preferably, quit using them.

Warning: The version of provider name in configuration is older than the current version. The value in configuration will be used, which may cause connection issue.

Fix: Change the provider name in the configuration file mentioned in the error message from "SQLNCLI10.1" to "SQLNCLI11".

That Should Do It

You're on your own now! Don't forget to log your problems and solutions in the comments for this post.

No comments:

Post a Comment