Oracle to SQL migration error using SSIS

Question

Oracle to SQL migration error using SSIS

rd2718

Old Hand

Points: 330
More actions
November 23, 2010 at 1:25 pm

#144163

Hello friends
I am using SSIS for oracle to SQL migration. I am using Oledb source and Oledb destination and a data conversion task is being used. But i am getting the following errors:
1)[OLE DB Destination [22]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid date format".
2)[OLE DB Destination [22]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (35)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (35)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
3)[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (22) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (35). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
4)[OLE DB Source [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
5)[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. 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.
IS this some date format issue?
Please suggest ASAP
tHANKS IN ADVANCE

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 1

Well, apparently your dates don't have the correct format. Inspect your data at the source or in the dataflow with a data viewer. If necessary, write error rows to a dump so you can inspect them.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rd2718 Old Hand Points: 330 More actions · Answer 2

thanks dazero

i did not get your point of writing the error rows to dump would you elaborate please? actually i am new to ssis

thanks

LSZ Grasshopper Points: 18 More actions · Answer 3

Hi,

For example "Data Conversion" transformation has 2 arrows available, one is green for successful transformation (and you can connect it to some destination) and the other one is red which when connected as an input to eg. "OLE DB Destination" can redirect rows which did not succeeded the type conversion in "Data Conversion". All you have to do is just select all rows in "Configure Error Output" screen which appears when you connect red arrow to destination and select "Redirect row" and hit Apply.

HTH,

Lukasz

rd2718 Old Hand Points: 330 More actions · Answer 4

thanks du2y

I did the same as you said first i did it on source but the package was failing then i did at the destination by connecting the error output to the flat file or also tried to the table with same schema in same DB but the thing is package is succeeding it shows that so many rows are going into the flat file or Table but when i see that there is nothing in those destinations.

So wt is the issue in your view?

Thanks