November 21, 2008 at 6:37 am
Hi All,
I am new to SSIS and trying convert string data coming from a flat file source to int using data conversion transformation but it gives me the following error message,
The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
I guess the better way is to go with the derived column transformation but I am not good the expressions. Can any one please help me with it?
thanks,
Sameer
November 21, 2008 at 8:32 am
I think the error is fairly clear, you have some data in the flat file that is too big for the int datatype. What I normally do in these instances is configure the error output to redirect the row so I can identify the data that is causing the error and then determine how to fix it. It may be in your case that you need to use a different data type.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 21, 2008 at 8:49 am
I have tried both the data types available under the data conversion transformation i.e, DT_numeric and integer but none of them worked. However i figured out that using,
LEN(TRIM(column_name)) > 1 ? (DT_I8)column_name : 0
expression in derived column transformation does the trick.
November 21, 2008 at 9:03 am
Ahh, so the problem was blanks/empty strings.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply