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:
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:
and replace it with:
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: