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?

  1. Fix the Oracle driver installation.
  2. 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.

Oracle Developer Tools for Visual Studio

In my previous blog, I was somewhat hard on Oracle for not testing their connectivity software property. On a positive note, I like their effort to integrate Oracle Developer Tools with Visual Studio. This video provides a nice intro to this feature. Basically, Oracle Developer Tools for Visual Studio fulfills the same role as the Visual Studio built-in support for SQL Server, including designing objects, querying databases, and even tuning queries in Server Explorer.

It’s great to see vendors working together and Oracle Developer Tools for Visual Studio is a true affirmation that times are changing for better.