Null values when importing from Excel with SSIS

I’ve written SSIS packages that import data from Excel spreadsheets and load them into SQL. However, I recently came across a situation where half the data in one of my columns were having their data values ignored and was written to the DB as Nulls. I tried changing the excel column format, etc with no luck. Perplexed I did some searching and came across an interesting little quirk. Basically when you load a column it reads the first 8 rows and tries to determine what it should load the data values as. Now if your data has a mix of characters and numbers, you need to make sure you reflect this in the first 8 rows or it will not load correctly. The official explanation and workarounds can be found here.

Advertisements