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