April 19, 2019 at 2:50 pm
I'm out ideas on the following. Would appreciate anyone's additional tips or suggestions if you have any.
I've developed 3 SSIS pkgs using VS2017. The pkgs serve to replicate data from numerous tables in SQL Server to equivalently - structured tables in Oracle 12c.
I'm using the Microsoft OLE DB Provider to connect to Oracle initially to truncate some Oracle staging tables & this is working fine when executing the pkgs on my development PC.
I have had zero success getting the pkgs to run on a different machine where SQL Server 2012 is installed. I constantly get error messages reporting various things about not being able to connect to the MSDAORA source.
Im using the Microsoft ODBC Oracle Provider at the tail - end of the pkgs to actually do the inserts from Oracle staging tables into the permanent Oracle tables and that's working fine on my development PC.
I have the 32-bit & 64-bit Oracle Clients installed on my development PC.
I would like to move away from the Microsoft OLE DB Provider for Oracle for my SSIS connections into Oracle within SSIS and instead go with the Oracle OLE DB Provider.
I can see on my development PC where I have an "Oracle Home" for the 64-bit ODAC. I think I also have the ODAC installed for 32-bit, however, I don't know/see how to verify this.
I currently do *not* see an item for "Oracle OLE DB Provider" if I try to setup a new OLE DB connection manager within SSIS. I have no idea how to make that item show - up within the VS2017 development environment.
The DBA that knows the ins & outs about the SQL Server machine verified that the ODAC is installed on it He's also verified that the 32 & 64 bit Oracle Client is installed on that machine.
It is confusing to me that all of what I read states that the Microsoft OLE DB Provider for Oracle (MSDAORA) will not work for data connectivity to Oracle past Oracle version 9. On my development PC, I'm using this provider and successfully doing whatever I want to data within Oracle version 12c. Don't understand that.
So, most everything I can read about that is supposed to be present/installed on a SQL Server machine in order for connectivity into Oracle from an SSIS pkg to execute successfully is there, but again my packages will run with zero issues on my development PC but fail on the SQL Server machine.
Any thoughts or suggestions are appreciated. Have exhausted most all efforts I can read about or think of on this issue without any resolution.
April 23, 2019 at 3:36 pm
Are the connections to the Oracle database set to use a Windows AD Account or a Database User Name and Password?
If using the later what protection level is set for the Project and each SSIS package? eg. Don't Save Sensitive, Encrypt Sensitive Data with User Key, etc.
April 24, 2019 at 12:14 pm
Do you have the full ODAC installed or the xcopy only?
I always install the 64-bit odac first and later on the 32-bit (for visual studio) in a different home
2. reboot
3. ODAC 12.2c Release 1 and Oracle Developer Tools for Visual Studio (12.2.0.1.1)
4. reboot
Make sure to have Oracle Provider for OLE DB 12.2.0.1.0, Oracle Data Provider for .NET ... checked
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply