Importing Excel / XLS into SSIS?

Tips, Tricks, Pitfalls & Best Practice please:

in other words

e.g.

Do use this connection

Don't use this datatype

Do sort X

Don't open over network

Z gets corrupted when开发者_JAVA百科 Q is likely

Don't use "," use "|" as separator

Don't use 'wizard' that determines column datatype by sampling n rows Don't use multiple sheet

(the above list is not meant to be a guide, just me thinking of random stuff!)

There are some Excel connection managers on codeplex. Running on a 64 server needs to use the 32 bit DTEXEC. Looks like they have a 64 bit provider, but it's for sale from CozyRoc. There is the Excel2 manager also, which looks free.

You are right about column datatypes and problems with row samplings.

Sometimes it's easier to save the Excel file to CSV and import that.