SSIS 2012 Microsoft Connector for Oracle Upgrade Issues

For anyone that uses SSIS to get data from Oracle data sources you have probably had the pain of working with the components provided by Microsoft in the default installation of SSIS. You may have even downloaded the free Oracle tools for Windows that provide some more options for connecting to Oracle. Both of these choices are ok and may work fine in your environments, but for the best solution Microsoft worked with Attunity to make their connectors available for everyone to use without any additional licensing cost (FREE)! These connectors have been considered the best Oracle connectors that you can use with SSIS for quite awhile now. When SQL Server 2012 was released in April v2.0 of these connectors were also released, along with an updated v1.2 for older versions of SSIS.

Now for the twist in the story, what is the upgrade path if you are currently using Microsoft Connectors v1.1 for Oracle by Attunity (wow, that is a mouth full) and you want to upgrade your SSIS packages to SSIS 2012? Logic would tell you that you should be able to install SSIS 2012 and the Microsoft Connectors v2.0 for Oracle by Attunity and then just run the upgrade wizard in SSDT (SQL Server Data Tools) to get your packages up and running in SSIS 2012 and latest Oracle connector, right? Not so fast, unfortunately if you try this your package will be upgraded to SSIS 2012, but your Oracle components that you created using the older versions of the Attunity connectors will no longer work and you may not be able to edit or even delete them from your package! Below is a screenshot that shows what your component will look like after the upgrade:

You will also see errors, similar to this:
Error loading xxx.dtsx: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "Oracle Source;Microsoft Connector for Oracle by Attunity; Attunity Ltd.; All Rights Reserved; http://www.attunity.com;2".

Error loading xxx.dtsx: The component metadata for "Oracle Source" could not be upgraded to the newer version of the componenet. The PerformUpgrade method failed.

You will not be able to edit the component to try and fix it and depending on how many of these components you are using in each package you may even see this error message when you try to delete the component from your package:

So, if you are unable to edit or even delete these invalid components from you package, what do you do? The only option available to you at this point is to edit the .dtsx file in a text editor so that you can see the XML, but if you go that route and you are not very familiar with XML structure or how the XML for SSIS is setup you might end up making a bigger mess.

Thankfully, after a week or so of fighting this same issue for a client that I'm currently working with we were able to get a solution from Microsoft Support that is very quick and will prevent you from losing any of your query, mapping or metadata! The fix is to use a text editor (I prefer Notepad++) and you can do a search and replace for the following GUID in your packages:
{4CAC6073-BCA7-430E-BD29-68A9F0012C6D}
and replace it with:
{CB67CD40-126C-4280-912D-2A625DFAFB66}
The first GUID should be unique to v1.1 of the connector, so if you have upgraded to v1.2 that GUID may be different.

Once I completed this on all 120+ packages that used the Oracle connector and reloaded the updated versions in SSDT all of the error messages went away and the data flows appeared correctly as shown below:

I am not sure if this will be a continuing issue with these connectors as new versions are released or if this is because of the major changes required going from previous versions of SSIS to SSIS 2012. Either way, I wanted to make sure this information was out there for those are upgrading to avoid some of the problems we had.