Data Load - How do I create a derived column that will bypass blanks (Nulls) and remove periods (.); convert from varchar to date in SSIS

• leonie6214

  SSC Eights!

  Points: 989

  More actions

  September 21, 2015 at 8:09 pm

  #307819

  I am having difficulties loading data from a flat file to a SQL Database. I am able to load some data but the rest gets kicked out for the following reasons:

  1 – The field is varchar 50 and I would like to convert it to a date field

  2 – The field contain periods (.) (Only 1 period in each row)

  3 – The field contain blanks (NULLS)

  How do I create a derived column that will bypass blanks (Nulls) and remove periods (.) in each row then convert column to a date field in SSIS? Can someone provide the steps to create a derived date column using SSIS (derived task); convert it to a date column (09-19-2015); use functions to redirect the nulls and possibly remove the period (.)?

  Any help will be greatly appreciated.

  Sample Data

  Column 3 (Varchar 50) Need to convert to date; remove periods, and bypass nulls(blanks)

  Blank

  .

  Blank

  .

  Blank

  Blank

  .

  01-19-2015

  01-19-2015

  Blank

  .

  Blanlk

  .

  Blank

  01-19-2015

  .

  Blank

  .

• michal.lisinski

  SSCertifiable

  Points: 7201

  More actions

  September 22, 2015 at 7:11 am

  #1828381

  Hi

  Well, actually, you could do it the other way, please try my example:

  FINDSTRING([Column 3],"-",1) == 0 ? NULL(DT_DATE) : ((DT_DATE)([Column 3]))

  Br.

  Mike

• reddydinesh.n

  SSC Rookie

  Points: 40

  More actions

  September 22, 2015 at 7:46 am

  #1828404

  Hi,

  You can do like this way.

  Drag and drop a derived column task. Add new column with the conditional expression as:

  ([Column3]=="")?DT_DATE(NULL): (([column3]==".")?DT_DATE(NULL):[Column3])

  In the DFT destination mapping use this new cloumn created in Derived column task to map to destination.

  Hope it works 🙂

• leonie6214

  SSC Eights!

  Points: 989

  More actions

  September 23, 2015 at 5:51 pm

  #1828984

  Thanks you both for your help. I was able to resolve the SSIS issue.

  Regards,

  leonie6214