DataType Conversion

• Syed Ijaz

  SSC Enthusiast

  Points: 159

  More actions

  November 17, 2006 at 12:34 pm

  #174962

  I have a flat file source and am trying to convert a column type from string [DT_STR] to four-byte signed integer [DT_I4] before loading it into the destination. I am getting the following error message:

  Error: 0xC02020A1 at Data Flow Task, Flat File Source [1]: Data conversion failed. The data conversion for column "contactID" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

  Here are the sample values for the column contactID:

  113

  114

  115

  116

  At the destination, this column is defined as ContactID(int, not null). But the numbers are coming from flat file as [DT_STR] and I would like to convert those to four-byte signed integer [DT_I4] before finally loading them into the destination.

  I have already tried Data Transformation Task and Derived Column Task without success.

  How can I acheive this?

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  November 17, 2006 at 12:47 pm

  #672737

  Insert that data in a working table with a varchar(20) column.

   

  Then run this : SELECT * FROM dbo.TempTable WHERE ColName LIKE '%[^0-9]%'

   

  It will scan for any non-number characters.  If no rows are found, then it's because you have some numbers which are greater than 9999 (not Between '0000' AND '9999').

  Once you find the problem data you'll see what you have to do to fix it.  Then load from working table to permanent table.

• Suresh Bose

  SSC Veteran

  Points: 213

  More actions

  November 21, 2006 at 11:21 pm

  #673386

  Hi,

     One reason may be that you are getting some alphanumeric value (like A232, B399N) which fails during Conversion.

      Please configure an error output for the Data Conversion task and use a data viewer to see why the conversion fails. Once you find the reason, you can attack the issue accordingly.

  Thanks

  S Suresh

• Jan-155192

  SSCrazy

  Points: 2613

  More actions

  April 12, 2007 at 3:09 am

  #699988

  Hi all,

  I'm experiencing the opposite problem: I'm importing an excel with numeric data and an error in one value which is a string. I'm using the "Data Conversion Transformation" in SSIS. Now I wanted to redirect all rows in error with the "Configure Error Output". But I don't receive any output. The incorrect data just seems to be skipped.

  I would like to log incorrect data. Any ideas?

  Thanks,

  Jan