I had a simple SSIS flat file import going but the empty date fields were not showing up as null but "1899-12-30 00:00:00.000".

Short answer:

• Right click "flat file source" component
• Select "Show Advanced Editor" from the context menu
• On the "Component Properties" tab set "RetainNulls" to true

I knew it was hidden somewhere! This post helped - http://phil-austin.blogspot.com/2007/12/retain-nulls.html - my issue was all the same except for the date value.

We are managing lots of "lookup data" through a big fat Excel spreadsheet. We have a simple tool that reads the sheets and produces insert statements (using ADO.NET).

The problem was that some fields were coming through NULL when I knew they were not.The short answer is "IMEX=1"

Basically the provider looks at the first few rows and guesses the type from the cell values, in my case the first few were numbers followed by text codes. When the provider gets to the text values its in “number” mode so defaults to a NULL value.

You can use the "IMEX" property to make the provider look further and go into an import mode. See the KB article for more info.

http://support.microsoft.com/kb/194124

PK  :-)

 

PS: To connect to an excel file within ADO.NET use the "System.Data.OleDb" provideran a connection string similar to:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source="foo.xls";Persist Security Info=False;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"