SSIS and null into int column

• sqlfriend

  SSC Guru

  Points: 52794

  More actions

  January 11, 2012 at 2:05 pm

  #146795

  I have a ssis pacakge. that import from flat source file to SQL table.

  The flat file is generated from another system. I want to import to sql table.

  We have a column in SQL table is PersonNumber as data type integer, and Nullable.We want to have null values instead of 0 in the table.

  The flat souce file in that column has values and also blank values.

  which we want to convert to null.

  I used a derived column drvPerno= TRIM( [PerNo] ) == "" ? NULL(DT_WSTR,1) : Trim( [PerNo])

  it succeeded to run in the step, but failed at the OLEDB destination, the eror is cannot convert , it cause data lose, I add a debug for showing the error output for OLEDB destination , it shows those records with perno as empty string values records there.

  The thing I don't understand is it has already been converted to Null, but why they cannot be inserted into database?

  I also tried to set it to 0 if '', converstion data view runs fine, just when get to the SQL table, it gives error: the data balue cannot be converted fro reasons other than sign mismatch or dataover flow

  Thanks

• Martin Schoombee

  SSCoach

  Points: 19135

  More actions

  January 11, 2012 at 2:43 pm

  #1432153

  It is because you are passing a null value of the wrong type. You cannot use DT_WSTR in this case, because the destination field is expecting an integer value.