SSIS, load file into table with '' (nothing) to get NULL

Question

I have file with 150 columns and most of them are nothing, represented like 2 consecutive delimiters (100,,,,,,200) in this case comma is delimiter so Column0=100, Column1='', Column2='', etc...

What is the fastest (afraid to say mass) way to put this into target table, (and target table has custom DDL can not change, and I have to put in correct way have NULL for date, not 1900-1-1). I have series of this files, I can go with SSIS and put isNothing ? NULL : Column1 for each of 150 columns, but maybe there is better way? I tried to load this file into NEW table and got intermediate table with same Nothing, so now I can compose sql with CASE'ing too.

THanks for you help and sharing your knowledge. M

Answer 1

Did you tried to check the "Retain null value" box in the flat file source ?

Hope this Help,