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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s