Seeking Oracle Reloaded
Scenario: You create an SSIS 2012 project in SSDT that uses the Attunity Oracle connector. By default, Attunity creates a private connector. You verified that the package connects and executes successfully. Then, you promote connector to a project-level connector so that you can manage the credentials in the SQL Server job configuration across all packages. When you attempt to deploy to the SSIS catalog, you get the following error:
Failed to deploy project. For more information, query the operation_messages view for the operation identifier <X>
When you query the operation_messages view for that message_id, you get the enlightening reason:
Failed to deploy the project. Fix the problems and try again later.:Unable to create the type with the name ‘MSORA’.
Solution: I had my own fair share of Oracle OLE DB driver installation issues, some of which I documented here. I don’t know how this customer managed to mess up the Oracle driver installation (not that it’s hard to do) but the registry settings were off. How do we fix this horrible issue?
- Fix the Oracle driver installation.
- Remove and reinstall the Attunity drivers. It could be that in this particular case, the customer had both the Attunity 1.2 and 2.0 drivers and they removed the 1.2 version.
Note that you must install both 32-bit and 64-bit Oracle and Attunity drivers. That’s because SSDT (being a 32-bit application) users 32-bit drivers, while the SSIS runtime uses the 64-bit drivers unless you specifically configure the job to run as 32 bit.
