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