Using Excel as SSIS Data Source

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>


Leave a Reply

Fill in your details below or click an icon to log in: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: