1) Create a OLE DB connection.
2) Provider is Microsoft JET (note, this will only work on x86 machine or package running as x86)
3) Set filename to be full path to xls file.
4) user and pass remain default (are actually ignored and cannot be used for xls)
5) Click on All, scroll up to Extended Properties. paste in options I mentioned.
7) In data flow, create new OLE DB Source
8) Choose the connection you've just created.
9) Select tables to see what sheets you have exposed.
10) Change from table to SQL query. Select F1, F2, F3 etc from [myworkbook$]
11) Click ok.
12) Add rest of components (a union all as a consumer to test with)
13 Run 🙂
I can't die, there are too many people who still have to meet me!
It's not a bug, SQL just misunderstood me!