The data value cannot be converted for reasons other than sign mismatch or data overflow.

Question

Post reply

The data value cannot be converted for reasons other than sign mismatch or data overflow.

ramyaganesh11

Old Hand

Points: 316
More actions
March 8, 2010 at 4:25 pm

#145698

Hi,
Can somebody tell me that why I am I getting this error when converting string(staging table data) to four-bite-signed interger(DT_I4) using Data Conversion Transformation.
"The data value cannot be converted for reasons other than sign mismatch or data overflow."
Convert Data Types col1 -1071607681
Can someone help me?
Thanks alot
ramya

Viewing 7 posts - 1 through 6 (of 6 total)

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

TangoVictor SSChampion Points: 10975 More actions · Answer 1

why not just configure the error output to ignore failure. This way it will just out Null for non-numeric data.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

ramyaganesh11 (3/8/2010)
"The data value cannot be converted for reasons other than sign mismatch or data overflow."

The string's value probably is not a number. IE, "abc" cannot be converted to an Int4.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Also, it's a good idea in SSIS to have one or more "Bad Row" output files, that you use in the DataFlow to divert such records to. Makes it a lot easier to figure out what's wrong.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

malijala Valued Member Points: 73 More actions · Answer 4

Hi,

I had the same problem with decimal field, both staging en targeta

defined as numeric(18,2), changed source (staging) to real and the error disappeared.

Greetz,

Emmanuel.

Sushil Dwivedi SSC Eights! Points: 973 More actions · Answer 5

If the column is varchar and it has all numeric values than it can be converted to Integer by using data conversion but if it has single non numeric value than you will get error.

kumar.bhupendra SSC Enthusiast Points: 191 More actions · Answer 6

I faced the same issue and I solved it by :

converting the column which has having issue at source side to : DT_WSTR
and in the staging the colum data type I kept as nvarchar

It resolved the issue.

Thanks.