Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
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: Wednesday, September 16, 2015 10:42 AM
Points: 1,863, Visits: 3,477
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
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, September 22, 2015 6:49 AM
Points: 539, Visits: 4,169
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