NUMBER without Precision

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 Smile
Published Tuesday, April 28, 2009 3:47 PM by MartinIsti
Filed under: , ,

Comments

# re: NUMBER without Precision

Thursday, June 25, 2009 6:31 AM by ShortLived

While it did work the SQLCommand property for the DataReader source doesn't suuport expressions.  I need to be able to dynamically change the WHERE clause and with the OLE DB soource I can use SQL Command from a varaible as the AccessMode property.  

Is there an easy way using the script task to set the SQLCommand property of the DataReader source?

Thank you.

# re: NUMBER without Precision

Thursday, June 25, 2009 7:08 AM by ShortLived

Apparently I wasn't patient enough - I found the anwer on this link:

pragmaticworks.com/.../making-the-ssis-data-reader-source-query-dynamic.aspx

# re: NUMBER without Precision

Thursday, June 25, 2009 10:38 AM by MartinIsti

Thanks for the comment (I rarely have any :)) and for the link as well!

Powered by Community Server (Commercial Edition), by Telligent Systems