SSIS Oracle Connector returns Conversion errors for Number Data Types

[RonInOttawa]

Hello;

I'm running into a problem when a SQL Server Agent job runs a job that uses the Attunity Oracle Connector.

If I run the job manually (From BIDS) all works fine).
If I use the Microsoft OLE DB connector all works fine except that I need to run in 64-bit environment.

If I run the job from SQL Server Agent, I get the following error messages:

A conversion error occurred. Column name is 'TimeSpent'.

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OS - Extract Backbone Time Spent data from CodeCollaborator" (342) returned error code 0x80004005. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

I Tried CASTing the field and not CASTing it with the same result.

The data type in the Oracle database is set to number. I can't change it because it is a vendor database so we have no control.

Any help much appreciated.

[Gadi.Farhat]

Hi Ron,

To be able to help you, i need more information on how you use the Oracle Connector:
1. Is the error returned from the Oracle Source or Destination component?
2. If you are using the Oracle destination component then, is it configured to use Fast Load?
3. What is the NLS_LANG setting of your Oracle client?
4. What is the exact problematic column data-type (Precision and Scale)?
5. If you know which record is causing the error, then what is the 'TimeSpent' column value in this record.


Regards,

Gadi

[RonInOttawa]

I guess the whole idea of Forums is to help people work out their problems.

Well, you did, just by asking me all those questions.

For some odd reason, on our Dev Server, the NLS-LANG was not set. Dunno why, it just wasn't. I had our server admin set it and all works beautifully.

The weird thing here though is that I use the connect in 5 places in this package and it worked on 4 of the 5. Guess I never really pulled a number big enough to cause it to fail in the others.

Anyway, THANK YOU very much for your assistance.

Ron...