Error:The precision must be between 1 and 38.

Hi All

I am trying to pull data from Oracle to SQL Server but if I use Oledb Source than I get this error

Error at Data Flow Task [DTS.Pipeline]: The "output column "CUST_ID" (590)" 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 only solution I found is use DataReader Source,

But if I use DataReader Source everything works fine , I mean I am able to see the records and convert it desired data type (using Data Convertion component).

My question is what component should I use as Destination, coz if I use OLEDB Desination I get a red cross on the components although I can map all the columns.....

I believe you need to convert that column to have a precision of 1 or greater in the query that you use to get at the data. Search this forum for examples. You're not the first with this issue.|||

I fixed the error which I was getting with DataReader Source. Everthing is Ok at design time but I get this error at the source at runtime:

[DataReader Source [1]] Error: System.Data.OleDb.OleDbException: Oracle error occurred, but error message could not be retrieved from Oracle.
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo,
DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection

owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName,
String connStr, Object transaction) at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)

|||Try your query in SQL*Plus or some other Oracle client to see if it errors out there as well.|||

I've dealt a lot with this error. So far I've found two solutions:

1) Cast column to NUMBER(precision, scale) in Oracle select (eg. select CAST(columnName as NUMBER(10, 3)) as columName from ..

2) Cast to string in Oracle select (eg. select TO_CHAR(columnName) as columName from ..

For more info, check out this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1432727&SiteID=1

Another common problem when selecting from Oracle is getting the error "accessor is not a parameter accessor", which you get when precision from a calculation in the select exceeds 38. For example when you divide two columns. ADO can not handle the same amount of precision that the Oracle select can handle. Anyway, to overcome this problem you cast the column (or expression (division)) to char (TO_CHAR(....)) and then you specify precision and scale (preferred) when you do the insert or substring the result to fit the target column.

Code (VB.NET): Dim para5 As New OleDb.OleDbParameter("myParamName", OleDb.OleDbType.Numeric)
para5.Value = myReader.Item("myColumn")
para5.Scale = CByte(10)
para5.Precision = CByte(22)

More about accessor is not a parameter accessor (only one google hit, and is cached):

http://www.google.com/search?q=cache:ZVTf6GErxyIJ:www.dotnetforums.com/archive/index.php/t-398.html+Conversion+failed+because+the+Decimal+data+value+overflowed+the+type+specified+for+the+Decimal+value+part+in+the+consumer's+buffer.&hl=sv&strip=1

|||

I don't know what's wrong , this is driving me crazy....this package was working fine till last week n now it is giving error

[Source-AP [1]] Error: The AcquireConnection method call to the connection manager "MS_OLEDB" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "Source-AP" (1) failed validation and returned error code 0xC020801C.

I am able to see the data preview and column mapping etc