The data of several tables was already imported successfully when I encountered the following error message for one table:
Msg 7354, Level 16, State 1, Line 1The OLE DB provider "OraOLEDB.Oracle" for linked server "SERVERA" supplied invalid metadata for column "WMBS_TIMESTAMP". The data type is not supported.After excluding this column I still got the same error message, so I turned to Google and arrived at MS support.
Message 7 at the "Common error messages and how to troubleshoot them" section brings me a bit closer as it seems to address my issue.
It brings me to the following Microsoft Knowledge Base article: 243027. However, as you can read in the name of the KB: it handles Numeric columns in Oracle, while my column is a TIMESTAMP. That leads me to the definition of an Oracle TIMESTAMP data type:
The default is 6. So there are 6 digits of fractional seconds in the Oracle part of the data. However, SQLServer only has defaults of 3 (DATETIME) and 7 (DATETIME2, DATETIMEOFFSET). I think the query engine tries to convert it to a DATETIME2 and fails because of the default precision.
To solve this I used OPENQUERY in which I specified the query and explicitly CAST the value of the specific column to a DATETIME2 data type.
,CAST(WMBS_TIMESTAMP AS DATETIME2)
FROM Schema.Table ')