Data conversion transformation Error

Question

Post reply

Data conversion transformation Error

chatwithkrishan

Ten Centuries

Points: 1056
More actions
May 12, 2009 at 6:44 am

#209298

i have values 1124015359 as string .
i want to convert as int data type by Data conversion transformation in SSIS. i used the eight digit single integer as a convert data type.but i got error. how to resolve this ???
[Data Conversion [897]] Error: The "output column "Copy of Column7" (3397)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Copy of Column7" (3397)" specifies failure on error. An error occurred on the specified object of the specified component.

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

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

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 1

are you sure you only have numeric integer values in that column?

try running the following SQL with the column and table names changed to match your schema:

select isnumeric(replace(columnname,'.','z')), count(*)

from tablename

group by columnname

chatwithkrishan Ten Centuries Points: 1056 More actions · Answer 2

hi,

thanx for reply.

i have only integer value in the flat file.

some of the records haev empty value also ..

steveb. SSC-Forever Points: 46733 More actions · Answer 3

change the error settings on the data transformation to re-direct the rows in case of an error. Send these error rows to a table or flat file and then this will show which rows are causing the problem..

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 4

it could well be the empty values which are causing the error

use the data conversion to convert these to a 0(zero)

chatwithkrishan Ten Centuries Points: 1056 More actions · Answer 5

hi,

i created a new variable(scope:package) 'xx' as string with value of 2

i can get the values in script component. when i convert that as interger, it wil give the error .

[Data Conversion [897]] Error: Data conversion failed while converting column "xx" (2828) to column "Copy of xx" (2871). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

one more thing.

even if i convert as string "xx" i can t get the value as "2" . it will give "0"

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 6

I used this data:

123435

1124015359

1234

1

2

3

4

and created a new derived column using the following:

([Column 0] == "" ? "0" : [Column 0])

and it worked fine

steveb. SSC-Forever Points: 46733 More actions · Answer 7

Samuel Vella (5/12/2009)
I used this data:
123435
1124015359
1234
1
2
3
4
and created a new derived column using the following:
([Column 0] == "" ? "0" : [Column 0])
and it worked fine

be careful with this sort of conversion as you are assuming that an empty (possiblu Null) value equals 0, when 0 may have a business meaning that is different from Null.