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.