SSIS: Get Excel file's sheets programatically

Question

My app generates SSIS packages for importing data from Excel files. This is done programatically, using C# .

Before the package is generated, there's a setup step, in which I allow the user to select, from a template Excel file, the sheet name and columns to import. (Basically what you would normally do if you were manually building the package using BIDS/VS and you were setting up your Excel Source component).

I do this by creating a temporary SSIS package with an Excel connection manager, Dataflow task and Excel source.

Using this method, I have no problem reading the column names using the SSIS API once I've specified the sheet name. However I can't seem to find a way to get a list of sheets with what the SSIS API gives me.

I can enumerate the sheet names using an OleDbConnection (as shown here), but is there any way to do it using the SSIS API? Is that even the direction I should go, or should I use OleDB exclusively for the initial setup and the SSIS API strictly for generating/executing the final packages?

Thanks

Answer 1

This is the closest thing to what I want that I've been able to find.

A possible solution is to create a similar package in BIDS (since I've realized it's completely unnecessary to create this package programatically in the first place) then interact with it (or its results) in my C# code to get the data I need.

However, I think it will be easier if I just use the OleDB provider for the setup and use SSIS API only for generating/executing the package once I've figured out what it will look like

Answer 2

Use package builder wizard templates. It will show you how to use script task to do the same job you trying

http://pragmaticworks.com/products/Business-Intelligence/BIxPress/