This week I experienced an annoying incompatibility between SQL Server 2005 and Oracle 9.2.2. If a column in Oracle has a NUMBER data type without explicitly set precision (like NUMBER(10)) then SSIS is not able to handle it when using an OLE DB Connection Manager.
The error message is this:
Error at Data Flow Task [DTS.Pipeline]: The "output column "column name" (136)" has a precision that is not valid. The precision must be between 1 and 38.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204018 (Microsoft.SqlServer.DTSPipelineWrap)
The exact settings are:
Oracle OLE DB Provider
OLE DB Data Source task in the Data Flow
How to solve it? Well, you may use the Microsoft OLE DB Provider for Oracle and it seems to work, "only" the values are far from exact. Every single number differs in the SQL Server destination table from the ORA source. So that can be thrown out through the window.
Consequence: use providers created by Oracle for Ora databases!
Solution:
- use .NET Providers\OracleClient Data Provider
- drag a Datareader source into the DataFlow
- set the Connection Manager to the newly defined .NET data source
- on the Component Properties tab type in an SQLCommand (there is no such clicking the columns like at the OLE DB source)
- voila, the NUMBER columns can be read though they receive a Precision of 38
- this precision can be altered afterwards with a Data Conversion task
- you cannot modify the Output column's Data Type properties on the Input and Output Properties tab of the Datareader source though it would be useful
Hope it helps if you have the same tricky error
