April 18, 2019 at 12:13 am
So I'm nearly to the point of pulling my hair out... I'll explain.
I'm working on 3 separate SSIS packages that basically will serve to frequently replicate data from several SQL Server databases/tables to equivalently-structured Oracle databases/tables.
These SSIS packages execute just fine when executed via Visual Studio 2017 where I'm doing the development. Reads from SQL Server are fine and inserts/updates into numerous Oracle tables are fine.
I'm now reading that once I move the SSIS project/packages over to my SQL Server machine where these 3 packages will need to execute several times each day, that they likely won't execute successfully due to the fact that the Microsoft OLE DB Provider (MSDAORA) has apparently been depricated if you're running a more recent version of Oracle. We're running Oracle 12... I think Oracle 12c actually. It seems to state numerous places online that MSDAORA was updated up through Oracle 9 but is depricated for all Oracle versions more current than 9.
So, is this legit and do I now need to rewrite my 3 SSIS packages that currently work just fine as far as executing them within my Visual Studio 2017 development environment on my PC and instead use the Oracle OLE DB Provider in order for these SSIS packages to likely execute successfully on the actual SQL Server machine once the SSIS project/packages are deployed to that machine?
With that in mind if this is what I need to do, the Oracle OLE DB Provider is not listed in SSIS as far as when I try to look for & select that connection provider type when trying to setup a new Connection Manager within SSIS. What do I need to install to get that entry available to select? I already have the Oracle Client installed, so do I additionally need to install some "flavor" of the Oracle ODAC (Oracle Data Access Components) on both my development machine and the SQL Server machine where the SSIS packages will ultimately reside? If so, 32-bit ODAC?
Depending on the responses and recommendations I receive, I may have additional follow-up questions, but that's my frustration right now and the questions I have for the moment.
I sincerely appreciate any responses or points in the right direction if anyone has time to offer any.
April 18, 2019 at 8:03 am
you need to install the correct components from Oracle client - if your workstation is 64 bit you need both 64 and 32 bit Oracle client.
when installing (Custom installation) you need to select a few options related to development - odbc, oledb and a few others - once you go through the list it will be clear which ones you need. Note that these are not installed by default and that is why you don't have them available at the moment.
for the server you will need the same bitness as your SQL Server (64 bit if a recent version) and you install the same options as you did on your workstation.
If you are using SQL Enterprise you may also consider using the Microsoft Attunity for oracle - this is faster than the other options. It still requires the Oracle Client installed.
April 18, 2019 at 1:32 pm
When you mention "Oracle Client", that's different to me than "Oracle ODAC". Or maybe that differing terminology is actually referring to exactly the same thing. Can you clarify this when you have a moment?
With my question below, we are running Oracle 64-bit version 12.1 0.2.0.
My assumption is that I need to install both this (64-bit) (http://bit.ly/2Gtd3qi) and this (32-bit) (http://bit.ly/2GtdLUu) on my development box?
And then just this (64-bit) (http://bit.ly/2Gtd3qi) on my SQL Server box where the SSIS packages will ultimately exist & run?
And also, you're basically saying to NOT do anything with the Microsoft OLE DB Provider for Oracle & to go exclusively with the Oracle OLE DB Provider from what I'm understanding? If that's the case, I have a few adjustments to make with my connection managers in my 3 SSIS packages to change them from the Microsoft OLE DB Provider to the recommended Oracle OLE DB Provider instead.
Thanks very much for your initial reply & your continued input if you have another moment to reply back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply