Converting string from csv file to an integer

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  January 28, 2009 at 10:05 am

  #155192

  I'm new to ssis. I have a csv file which has a column holding a string value as "2009" which I need to convert to an int to be inserted into a n int field in a datatable. I've tried the Data Conversion task but it didn't work. Any help is deeply appreciated.

• TangoVictor

  SSChampion

  Points: 10976

  More actions

  January 28, 2009 at 10:36 am

  #933985

  use a derived column and create a new column using the formula such as (DT_I4) TRIM( FieldNameOfStringData )

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  January 28, 2009 at 12:04 pm

  #934067

  I tried but I'm getting the following error:

  [Derived Column [394]] Error: An error occurred while attempting to perform a type cast.

  I added a conditional split with the following in as the condition to try to get around the above error

  ISNULL([Column 0]) || ISNULL([Column 1]) || LEN([Column 0]) > 10 || LEN([Column 1]) > 500

  to send bad records to records to another file but I still get the error

• TangoVictor

  SSChampion

  Points: 10976

  More actions

  January 28, 2009 at 1:18 pm

  #934115

  maybe there is a null or empty string

  try this in the derived column

  ISNULL( TextField ) || TextField == "" ? NULL(DT_I4) : (DT_I4) TextField

• TangoVictor

  SSChampion

  Points: 10976

  More actions

  January 28, 2009 at 3:31 pm

  #934272

  the winking emoticon is supposed to be a right parenthesis - stupid things...