Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

SSIS and null into int column Expand / Collapse
Posted Wednesday, January 11, 2012 2:05 PM


Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 1,982, Visits: 3,716
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
Post #1234368
Posted Wednesday, January 11, 2012 2:43 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 3:45 PM
Points: 646, Visits: 4,455
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.

Post #1234399
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse