2

How can we fix this issue? Am I missing any driver? Everything looks good but I am encountering this issue when I add OLE DB Destination for oracle provider.

Source: Microsoft OLE DB Provider for Oracle" Hresult 0x80004005 Description: "Unspecified error"

Source: Microsoft OLE DB Provider for Oracle" Hresult 0x80004005 Description: Data Type is not supported

enter image description here

Community
  • 1
  • 1
user10260750
  • 83
  • 1
  • 5
  • Looks like you cut off the useful piece of information from your image. It appears you're getting "data type invalid" or something like this. Start looking into your data types. – Jacob H Feb 04 '19 at 14:33
  • all the data types match – user10260750 Feb 04 '19 at 14:37
  • 1
    Dude start googling your error... lol https://www.experts-exchange.com/questions/24630982/SQL-Query-and-Oracle-Error-Data-type-is-not-supported.html – Jacob H Feb 04 '19 at 14:42
  • Post the error message as quoted text and explain the intent behind the DTS. Oracle providers have known issues for SSIS, data type mismatches or running in 32 bits mode, for example. – EzLo Feb 04 '19 at 14:56
  • are u able to see the image please tell me – user10260750 Feb 04 '19 at 15:28
  • Have you tried using the SQL command access mode and specifying the columns you need to use to find if any of them are having an issue converting their data type? – userfl89 Feb 04 '19 at 15:36
  • i did try I am receiving this "No column information received by SQL command?"TITLE: Microsoft Visual Studio ------------------------------ The component reported the following warnings: Error at Data Flow Task [OLE DB Destination [572]]: No column information was returned by the SQL command. Choose OK if you want to continue with the operation. Choose Cancel if you want to stop the operation. ------------------------------ BUTTONS: OK Cancel ------------------------------ – user10260750 Feb 04 '19 at 15:49
  • 1
    The Attunity Connectors work much better than the stock Oracle connectors. And they're free from Microsoft! https://learn.microsoft.com/en-us/sql/integration-services/attunity-connectors?view=sql-server-2017 – digital.aaron Feb 04 '19 at 17:59
  • I agree with @digital.aaron, check the following answer you may find it helpful: https://stackoverflow.com/questions/54320725/oracle-provider-for-oledb-missing-in-vs-2015-shell/54337473#54337473 – Hadi Feb 04 '19 at 19:21
  • Did you solved the issue? if the provided answer solved it then you should accept it or you must provide your own answer. If it is not solved yet just give a feedback – Hadi Mar 19 '19 at 21:11
  • I came across this because I have this same problem and the Attunity solution does work, except data access modes don't include "Table name or view name variable" or "SQL command from variable", meaning you cannot use dynamic SQL here, use parameters, create your SQL statement via expression, etc. This is a big limitation, I have found. Using Native OLE DB\Microsoft OLE DB Provider for Oracle: https://i.stack.imgur.com/OKYFt.png Using Attunity Oracle Source component: https://i.stack.imgur.com/VogPa.png – Ray Nov 20 '19 at 00:01

1 Answers1

1

The main exception you are receiving is:

Data Type is not supported

This exception is thrown due to some oracle data types that are not supported in OLEDB such as Timestamp.

Based on the Oracle Provider for OLE DB Developer’s Guide:

Fractional second is not supported for TIMESTAMP data types binding with Command objects.

Other data types may not be supported in OLEDB such as CLOB data type.

It is highly recommended to use Oracle Attunity connectors instead of OLEDB provider, it guarantees higher performance and it is more supported and have been selected by Microsoft to be included with SQL Server Integration Services

For more information you can check my answer on the link below:

Similar issues links

Hadi
  • 36,233
  • 13
  • 65
  • 124