This blog post will contain some of the lessons I have learned using Microsoft Excel as a source file for SSIS. It is still very much a work in progress.
The first thing I learned is that the Excel Connection Manager is somewhat limited, and that to get any real flexibility out of it, you need to use the OLE Db connector for Microsoft Jet 4.0. But that’s only good for the earlier version of Excel (up to version 2003), and you have to do some manual editing of the Connection String properties.
Try this: Right click in the Connection Manager try and select New OLE DB Connection. Click the New button and select Microsoft Jet 4.0 OLE DB Provider (from the Native OLE DB list). Click the Browse button to locate your Excel spreadsheet. Before closing this dialog, click th All page (on the left side) and scroll all the way to the top. Next to Extended Properties, type the following: Excel 8.0.
<more to come>