Im running SQL Server 2005 and having a problem importing a text file using SSIS.
I was using DTS to import an ASCII text file into a table in SQL 2000. The table has 2 fields: a primary key varchar(10), and a DateTime field which can be null. Everything was working fine with DTS, if the text file's date field was emtpy, i.e. (~20070914BM~, ), a Null value was placed in the table.
After I migrated to SSIS, the table shows a value of 1753-01-01 00:00:00.000 instead of a null value using the same text file as before. Scouring the net, I found that this value is the lowest possible value for a DateTime field.
To hopefully solve the problem, I tried making a new table with the same structure as the other table and manually entering in a record leaving the datetime null. Then using SSIS to export this value (from the new table) to a text file and importing that into the original table. It still loaded as 1753-01-01 00:00:00.000.
On the table structure, the default value of the DateTime field is set to use Null.
My question is why is it defaulting to this value and not using the null from the text file.
Thank you in advance.