SSIS connectivity to oracle DB

Question

Hi,

I am getting the below mentioned error in one of the task in SSIS package.

----------

Exception from HRESULT: 0xC0202040
Error at Populate data combined data [OLE DB Destination [81]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available.  Source: "OraOLEDB"  Hresult: 0x80040E21  Description: "Multiple-step 
OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Error at <Task Name> [OLE DB Destination [81]]: Failed to open a fastload rowset for ""<Table Name>"". 
Check that the object exists in the database.

-------------

I have Microsoft visual studio 2010 shell,  server management studio 2012 installed in my machine and also I have installed ODTwithODAC121012 and attunitySSISOraAdapters for the  DB connectivity. 

I tested the connection to the oracle DB and it is success.

Please let me know if there is something wrong with the connection setups. 

Thanks in Advance!!

Answer 1

What is the edition of the SQL Server you're using?

Also what version of Attinuity driver you installed?

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 2

Microsoft SQl server 2008 and Microsoft SQl server 2012 is installed in machine.

Microsoft Connector for Oracle by Attunity Version 1.2 

Answer 3

Microsoft SQl server 2008 and Microsoft SQl server 2012 is installed in machine.

Microsoft Connector for Oracle by Attunity Version 1.2 

I'm asking on the edition. Whether it is standard, developer etc

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 4

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0  Enterprise Edition (64-bit) 

Answer 5

Hi SikanyaCu,

Please make sure you have set the correct TargetServerVersion in SSDT project and ensure the version of debug is matched with your version of oracle driver. And Also you can connect to oracle via CMD on local machine.

Then try to use 'Oracle provider for OLEDB' instead of 'Microsoft Provider for Oracle'. Or use ADO.NET, check what happens.

Regards,

Pirlo Zhang

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 6

Hi,

Please check if the installed version of softwares are correct  for the below mentione version of visual studio and Sql server which is in my machine.

I am using 64bit- windows 8.1 enterprise.

Sql server : Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Microsoft visual studio : Microsoft visual studio 2012 shell version 11.0.50727.1

32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studiobase.
ODTwtihODAC : ODTwithODAC1120320_32bit

64-bit Oracle Data Access Components (ODAC) 
ODAC121010_x64 

Attunity :  I installed attunity 1.2 32 and 64 bit.

I am able to connect to the oracle DB and able to fetch the data. but the problem is in data access mode of the destination oracle server. It's throwing error when I set the data access mode as table or view - fast load.

Can anyone please help to resolve this issue. Is there any setting which I need to change in visual studio after installing attunity. 

Thanks in advance.

Answer 7

If the task is a Data Flow and you are querying Oracle try to set the AlwaysUseDefaultCode to True. It's just a try...but could be the problem.

Please mark as answer if this post helped you

Answer 8

Hi Diegoctin,

I tried it , but it's not working.

Please let me know if settings need to be changed after installing attunity.

Thanks,

Sukanya

Answer 9

• In the registry, under the key for an OLE DB provider's CLSID, there may be an entry named OLEDB_SERVICES. If the OLE DB provider that is used to make the ADO connection does not have the OLEDB_SERVICES entry, and ADO tries to set up a property that is not supported by the provider, the error occurs. For more information about this registry entry, see the "Resolution" section.

Just add the OLEDB_SERVICES and it will work

Please mark as answer if this post helped you

Answer 10

Hi Diegoctin,

Could you please let me know how to get the  OLE DB provider's CLSID ??

Answer 11

Hi SukanyaCu,

Is there any variables referenced in your connection string?

If so, try to set DelayValidation property to true in the Data Flow Task properties. 

Another possible way is to set ValidateExternalMetadata to false in OLE DB destination.

Or try to use Oracle Destination, see: Using the Microsoft Connector for Oracle by Attunity with SQL Server 2008 Integration Services

Check if the issue persist.

Regards,

Pirlo Zhang 

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 12

Hi Pirlo,

The issue is with data access mode - Fastload  option. I installed microsoft attunity 1.2, but the oracle destination does not show in the tool box. I am using visual studio 2010 shell (integrated) . In the tool box, oracle destination option is not there. Please let me know if I need to install visual studio enterprise edition.I have sql server 2008 enterprise edition installed in my machine. 

Answer 13

Hi Pirlo,

The issue is with data access mode - Fastload  option. I installed microsoft attunity 1.2, but the oracle destination does not show in the tool box. I am using visual studio 2010 shell (integrated) . In the tool box, oracle destination option is not there. Please let me know if I need to install visual studio enterprise edition.I have sql server 2008 enterprise edition installed in my machine. 

Attinuity connector comes with Enterprise Edition 

See system requirements here

https://www.microsoft.com/en-us/download/details.aspx?id=29284

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 14

Hi Visakh,

Sql server enterterprise edition - 2008 is installed, but fast load option not working.

Answer 15

Hi Visakh,

Sql server enterterprise edition - 2018 is installed, but fast load option not working.

Can you check if you've installed correct version of Attinuity as per this?

http://www.danielbowlin.com/ssis-optimization-techniques-part-10-ssis-oracle-source/

Also refer the other link for how to configure it to ensure you did the same steps

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 16

Hi SukanyaCu,

Try to manually install Attinuity assembly into GAC then refresh SSIS toolbox.

Check what happens.

Regards,

Pirlo Zhang 

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 17

Hi All, 

The problem was with the ODTwithODAC software.

Oracle Provider for OLE DB 12.1.0.2  supports the IRowsetFastLoad interface, which enables fast memory-based bulk-copy operations in the Oracle database.

I installed the ODTwithODAC which contains Oracle Provider for OLE DB 12.1.0.2 , it resolved the issues.

Thanks !!