Excel and SSIS – the problems and solutions

There are various problems with importing data from Excel files.

Today I came across an article from 2012 by Koen VerBeeck which has been re-blogged on the SQLServerCentral.com website which very succinctly summarises the problems and solutions:

http://www.sqlservercentral.com/blogs/koen-verbeeck/2015/07/10/reblog-whats-the-deal-with-excel-and-ssis/

Please note my comment at the bottom which I will repeat here:

One thing I would add is that if you are in a situation where changing registry settings is difficult or you don’t want to have to bother with changing settings every time you move machines/re-image machines i.e. you have to stick to the default of 8 rows being sampled. Then just add 8 dummy rows to the beginning of your Excel tables with the relevant type of data inserted – bunch of ‘A’s for strings, ‘9’s for numeric etc. – and then delete all the dummy records out with conditional split as mentioned. With this setup you can be certain the SSIS routine will work on any machine with default JET settings.

Advertisements

Author: James

IT Manager - Network, Web coding, MS SQL and Online Mapping expert

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s