Is there a way to set a default for a column in SSIS?

• maximost1

  Mr or Mrs. 500

  Points: 515

  More actions

  January 20, 2009 at 10:51 am

  #128063

  I am moving data from flat file text columns into money columns. The columns that have data in them convert fine, but there are four columns that currently contain only NULLs, data for those columns will be added later as needed (late payment fees, etc.). The database will be updated daily once the program is up and running so the columns are needed even tho they currently have no data. The flat file is taken from our legacy system each evening and I have no control over it.

  My question is using SSIS, is there a way to set those columns to a default of .00?

• ps.

  One Orange Chip

  Points: 29422

  More actions

  January 20, 2009 at 11:05 am

  #929831

  Use default value in the money column.

  create table #t1

  (

  col1 int,

  col2 money default 0.00

  )

  --drop table #t1

  insert into #t1 (col1)

  select 1

  union all

  select 2

  union all

  select 3

  select * from #t1

  Output

  =====

  col1 col2

  ----------- ---------------------

  1 0.00

  2 0.00

  3 0.00

  (3 row(s) affected)

  ---

  
  Pradeep Singh

• Mukti

  SSCrazy

  Points: 2826

  More actions

  January 20, 2009 at 11:16 am

  #929843

  You can use Derived Column transformation. Use Replace and try the expression:

  ISNULL([ColumnName])? ".00":[ColumnName]

  I haven't tried this myself by i guess depending on your datatype you can build your expression.

  HTH

  ~Mukti

• maximost1

  Mr or Mrs. 500

  Points: 515

  More actions

  January 20, 2009 at 1:20 pm

  #929932

  This appears to head in the right direction. Do you know how I can then get it to then convert from string to currency?

  I tried this

  ISNULL([Column 6]) ? (DT_CY)".00" : [Column 6]

  but it threw an error message saying the conversion would cause data loss.