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
Author
Message
Posted Wednesday, January 11, 2012 2:05 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:42 AM
Points: 1,749, Visits: 3,155
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
Post #1234368
Posted Wednesday, January 11, 2012 2:43 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, January 25, 2014 2:15 PM
Points: 317, Visits: 3,612
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