data type conversion using ssis
hello i am migrating from oracle to sql i am getting data type format error while importing data from oracle to sql for date datatype in oracle. I was using SSIS with data conversion task. IS there any method i can use to resolve the error with date format? Thanks in advance dimrd_SQL
November 29th, 2010 9:15pm

error is 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. Thanks dimrd_SQL
Free Windows Admin Tool Kit Click here and download it now
November 29th, 2010 9:47pm

oracle source table has datatype date and sql destination has set datetimedimrd_SQL
November 29th, 2010 10:02pm

yes for all other tables i did that but for 5-6 tables its not workingdimrd_SQL
Free Windows Admin Tool Kit Click here and download it now
November 29th, 2010 10:07pm

data sample values for some of the date columns in oracle are: RECENT_DATE ACTION_DATE TERMINATION_DATE 11/19/1992 6/2/1992 12/12/1992 4/9/1992 12/9/2009 6/9/2009 3/1/2010 10:34:28pm THERE are more date columns also. When i do import by using data conversion it import 1338 rows from 20000 rows and give error for rest. Thanks dimrd_SQL
November 29th, 2010 10:40pm

Arthurz I did that also using this expression (DT_DBTIMESTAMP)("20" + SUBSTRING([column], 7, 2) + "-" + SUBSTRING([column], 1, 2) + "-" + SUBSTRING([column], 4, 2)) but i am geting error? I am not confident in using derived columns thanksdimrd_SQL
Free Windows Admin Tool Kit Click here and download it now
November 29th, 2010 10:51pm

Thanks reza and arthurz i did as you said the package failed the same way with same error but while running the package nothing goes went into flat file. The colour of flatfile destination remained the same. but the no of rows at the destination changed from 1338 to 5352. Actually in the error configuration part of dataconversion task i selected redirect rows in the error and truncation columns. Is it right? thanksdimrd_SQL
November 29th, 2010 11:58pm

error with derived column while clicking OK on derived column properties i got is: Error: Error at data flow task[derived Column[1553]]: The function "SUBSTRING" does not support the data type "DT_DBTIMESTAMP" FOR parameter number 1. The type of the parameter could not be implicitly cast into a compatible type for the function. To perform this operation, the operand needs to b e explicitly cast with a cast operator. Error at data Flow Task[Derived Column[1553]]: Evaluating function "SUBSTRING" failed with error code 0*c0047089 Error at data flow task[derived column[1553]]: Computing the expression "(DT_DBTIMESTAMP)("20" + SUBSTRING([column], 7, 2) + "-" + SUBSTRING([column], 1, 2) + "-" + SUBSTRING([column], 4, 2))" FAILED WITH ERROR code 0*c00470c5. The expression may have errors , such as divide by zero, that cannot be detected at parse time, or there may be an out of memory error. Error at data flow task[Derived Column [1553]]: The expression "(DT_DBTIMESTAMP)("20" + SUBSTRING([column], 7, 2) + "-" + SUBSTRING([column], 1, 2) + "-" + SUBSTRING([column], 4, 2))" on"output column "Derived Column 1" (1679)" is not valid. Error at data flow Task [Derived Column[1553]: failed to set property "Expression" on " output column " Derived Column 1" ( 1679)" is not valid. Error at data flow task [Derived Column [1553]]: failed to set property "Expression" on"output column "Derived Column 1" (1679)". Additional information: Exception from HRESULT: 0*c0204006(Microsoft.SqlServer.DTSpipelineWrap) Its not on execution. Thanks dimrd_SQL
Free Windows Admin Tool Kit Click here and download it now
November 30th, 2010 12:15am

reply the dates on the source lies between 1992 to 2011. So i think its in the limit of SQL Server. thanksdimrd_SQL
November 30th, 2010 12:16am

reza Only source and destination go red when i execute with or without the error O/P to flat file. Data conversion transform turns to green. but the flat file remain the white. there is no data in that. Actually in the error configuration part of dataconversion task i selected redirect rows in the error and truncation columns. Is it right? Thanks dimrd_SQL
Free Windows Admin Tool Kit Click here and download it now
November 30th, 2010 12:31am

reza you mean i should attatch flat file to the source rt? and in the oledb source editor i should choose the error o/p columns "error and truncation" to redirect row. Is it rt? Thanksdimrd_SQL
November 30th, 2010 12:46am

reza you mean i should attatch flat file to the source rt? and in the oledb source editor i should choose the error o/p columns "error and truncation" to redirect row. Is it rt? Thanks dimrd_SQL yes, set error output for all columns which has DATE values inside. Reza I did the same you told. I just attatched the error o/p from the source to flat file by setting et error output for all columns which has DATE values inside to REDIRECT COLUMNS. bUT AGAIN I AM NOT GETTING ANYTHING IN the text file. the error i am getting is: --[OLE DB Destination [1976]] 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". --[OLE DB Destination [1976]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (1989)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (1989)" 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. --[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (3079) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (3092). 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. --[OLE DB Source [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. --[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. What shd i do now? Thanks dimrd_SQL
Free Windows Admin Tool Kit Click here and download it now
November 30th, 2010 1:44am

nevermind, look at row 1338 or 1339 in the source table and let us know what date values exists there, I think this point is where you hit error and you can find starting point here. http://www.rad.pasfu.com Reza I saw the values the rows are not continuous the are in increasing order but random. what does it mean? thanks dimrd_SQL
November 30th, 2010 3:44am

yes i had not put any order by clause.in source. Do you want me to connect flat file destination also on the source. thanksdimrd_SQL
Free Windows Admin Tool Kit Click here and download it now
November 30th, 2010 4:22pm

did you add any order by clause in your SOURCE in data flow? if no, put a sql query with order by clause and then find bad rows number. raza i did the way you said i used this query select * from mic.client order by ACTION_DATE what i saw on the destination side is the action date increases 1992 to 2006 then start fluctuating increasing and then decreasing. IS this give some clue? Thanks dimrd_SQL
November 30th, 2010 5:29pm

On this table i am still finding but i was lucky to find on some other table on which the the error was the same. So what shd i do with this table now? there are few more but now i have this culprit so once i tackled this i will try same for others thanksdimrd_SQL
Free Windows Admin Tool Kit Click here and download it now
November 30th, 2010 7:59pm

the way i am configuring the error o/p is that in OLEDB source editor i am choosing redirect rows for date columns in error and truncation. is it right? i have found wrong date values on another table which was giving the error. The values are 2500 and 1006 for year So how to make that work? Thanks dimrd_SQL
November 30th, 2010 8:47pm

i have found wrong date values on another table which was giving the error. The values are 2500 and 1006 for year So how to make that work? dimrd_SQL
Free Windows Admin Tool Kit Click here and download it now
November 30th, 2010 9:55pm

That's what I asked you to do yesterday (I knew there will be bad data in there). From here you decide, if the data needs to be corrected (at the source, not in SSIS and/or reported) or ignored. Business rules dictate here what to do. If you (for now) exclude the offending data all will work. A common practice is to check the values and redirect the bad rows. Arthurz thanks I did not get your point of check the value and redirect the bad rows would you elaborate it please? Thanks dimrd_SQL
November 30th, 2010 10:09pm

Sure, the idea is to eliminate the potential for failure (always a good idea, not only in SSIS). So to approach this you should use something like (to give Reza a credit I am using his blog post):http://www.rad.pasfu.com/index.php?/archives/23-How-to-handle-Failed-Rows-in-a-Data-Flow.html This is a "classical" example. Let us know if you need any further assistance. Besides, I am for including an IsDate() function in SSIS to help sift through the badly formatted dates. Arthur I did the same as the link of reza says Oledb destination table was Suppose products I made another table with different name productDUMMY WHEN I CONFIGURED THE ERROR O/P OF SECOND OLEDB DESTINATION WHICH POINT TO THE productDUMMY table in the same DB TO FAIL COMPONENT THE PACKAGE FAILS WITH SHOWING RED FOR OLEDB SOURCE AND FOR THIS SECOND OLEDB DESTINATION WHICH POINT TO THE productDUMMY table. NOW I CHANGED IT TO IGNORE FAILURE ALL COMPONENTS GREEN I WAS DANCING. BUT I FOUND THAT THERE ARE 9 ERROR ROWS TO SECOND OLEDB DESTINATION WHICH POINT TO THE productDUMMY table. BUT WHEN I CHECKED THERE WAS NOT ANY. ON THE WHOLE WITH YOU AND REZA I AM NEAR TO0 TEH SOLUTION JUST I THINK NEED LAST ADVICE BECAUSE I WANT TO CAPTURE ERROR ROWS TOO. THANKS dimrd_SQL
Free Windows Admin Tool Kit Click here and download it now
December 1st, 2010 1:04am

Arthur I did the same as the link of reza says Oledb destination table was Suppose products I made another table with different name productDUMMY WHEN I CONFIGURED THE ERROR O/P OF SECOND OLEDB DESTINATION WHICH POINT TO THE productDUMMY table in the same DB TO FAIL COMPONENT THE PACKAGE FAILS WITH SHOWING RED FOR OLEDB SOURCE AND FOR THIS SECOND OLEDB DESTINATION WHICH POINT TO THE productDUMMY table. NOW I CHANGED IT TO IGNORE FAILURE ALL COMPONENTS GREEN I WAS DANCING. BUT I FOUND THAT THERE ARE 9 ERROR ROWS TO SECOND OLEDB DESTINATION WHICH POINT TO THE productDUMMY table. BUT WHEN I CHECKED THERE WAS NOT ANY. ON THE WHOLE WITH YOU AND REZA I AM NEAR TO0 TEH SOLUTION JUST I THINK NEED LAST ADVICE BECAUSE I WANT TO CAPTURE ERROR ROWS TOO. THANKS dimrd_SQL
December 1st, 2010 5:52am

You can add a grid dataviewer to view the 9 error output rows. Please 'Mark as Answer' if found helpful - Chris@tier-1-support Could you see error rows with DATA VIEWER as Tier1 said? Reza andTire 1 i can see but when the package finish executing the the data from data viewer just go away. and while execution also i can see but not able to catch in excel sheet Thanksdimrd_SQL
Free Windows Admin Tool Kit Click here and download it now
December 1st, 2010 5:51pm

REZA RAAD and ARTHURZ i SALUTE YOU GUYS KNOWLEDGE. REZA YOU POINTED ME IN RIGHT WAY. ARTHURZ THANKS FOR YOUR EXPERIENCED SUGGESTIONS IT HELPED ALOT THANKS TIER 1 FOR YOUR FINAL THOUGHT. THANKS TO ALL WHO HELPED. dimrd_SQL
December 1st, 2010 8:52pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics