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

what is error message exactly?http://www.rad.pasfu.com

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

seems that you are importing a date value , and this caused problem what is datatype of this field in source oracle table and destination sql server table? http://www.rad.pasfu.com

oracle source table has datatype date and sql destination has set datetimedimrd_SQL

You will need a Derived Column Transformation to solve this. A good article to help you is by Todd: http://toddmcdermid.blogspot.com/2008/11/converting-strings-to-dates-in-derived.htmlArthur My Blog

ok, you can set data conversion transformation to convert date to DT_DBTIMESTAMP did you tried it ever? http://www.rad.pasfu.com

yes for all other tables i did that but for 5-6 tables its not workingdimrd_SQL

let me know sample of date value coming from oracle table. maybe you need apply expression on it.http://www.rad.pasfu.com

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

Then you need to go to that offending row and check the data fields for bad values, what did you find?Arthur My Blog

configure error output for data conversion transformation and connect error output to a flat file destination, let us know what rows you have in error output.http://www.rad.pasfu.com

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

It must be for the bad data out in there. Besides, what is the error you got?Arthur My Blog

Often times this error is encountered with data which is outside of the bounds for SQL datetime. Check to see if you have values below 1753-1-1. If so, they will throw an error. On another note, I don't know that I would trust the dates to always fall in the twentieth century. I would make sure to incorporate the century into your derived column (if you even need a derived column, which I don't believe that you do). As others have mentioned, simply capture the records that are giving you errors and see if you can find the common denominator.Please mark answered posts. Thanks for your time.

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

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? thanks dimrd_SQL yes, it is right, maybe error occured in the SOURCE, not the Data conversion transform, when you run the package what source/transform goes RED ?http://www.rad.pasfu.com

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

reply the dates on the source lies between 1992 to 2011. So i think its in the limit of SQL Server. thanksdimrd_SQL

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

so I think problem is in source, Configure error output on the SOURCE for all date columns and let us know resulthttp://www.rad.pasfu.com

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

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 If you read the error message you will note that the substring method is not allowed on the datetime field. So, you would first want to cast this to a string. However, you'll note that this isn't a string, which means that it is probably a datetime, which means that you are more than likely doing extra work for little return. At this point, you will want to compare the data type from the source and the destination. What are the differences in precision and range. After you determine this data you can attempt to replace the values that are causing the errors on you destination. Good luck!Please mark answered posts. Thanks for your time.

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.http://www.rad.pasfu.com

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

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

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

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.http://www.rad.pasfu.com

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

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

and after this , can you find BAD rows in the source table now? by the row number which error occurred there.http://www.rad.pasfu.com

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

It is weird why Configure error output on Source won't work for you, are you sure that you set it right? for all columns which has DATE value inside? if this will not help you , you can browse source table data and check values manually if there is a Different DATE Value there ...http://www.rad.pasfu.com

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

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? Thanksdimrd_SQL

yes, it is right.http://www.rad.pasfu.com

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

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.Arthur My Blog

I am agree with ArthurZ you should use Redirect bad rows to diminish bad data from ETL process.http://www.rad.pasfu.com

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

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 My Blog

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

You can add a grid dataviewer to view the 9 error output rows. Please 'Mark as Answer' if found helpful - Chris@tier-1-support

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? Set the values to NULL since we are talking about business data and not the history and future of the World. Also, you can upload screen(windows) images to www.skydrive.com and just copy/paste them here for faster assistance. Kalman Toth, SQL Server & Business Intelligence Training; SQL Server 2008 Training

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

Could you see error rows with DATA VIEWER as Tier1 said?http://www.rad.pasfu.com

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

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