SQL 2005 SSIS: Looping through Excel Files and then looping through sheets in those files

[Database Administrator]

Hello all,

I have not yet run into this, but I imagine that I will at some point. So I
thought I would ask and find out so I know when the time comes.

I already know how to loop through multiple Excel files and have created
successful packages doing just that. But what I do not know is how to loop
through multiple Excel files and then loop through multiple sheets in each
of those files.

Any thoughts/suggestions on how to accomplish this?

Thanks for any help anyone can provide,

Conan Kelly

[Database Administrator]

On Nov 8, 4:10 pm, "Conan Kelly"
wrote:
> Hello all,
>
> I have not yet run into this, but I imagine that I will at some point. So I
> thought I would ask and find out so I know when the time comes.
>
> I already know how to loop through multiple Excel files and have created
> successful packages doing just that. But what I do not know is how to loop
> through multiple Excel files and then loop through multiple sheets in each
> of those files.
>
> Any thoughts/suggestions on how to accomplish this?
>
> Thanks for any help anyone can provide,
>
> Conan Kelly

Hi Conan,

Stick your data flow task into a loop that cycles through the sheet
names in your excel file(s) (if you are looping the files using a
recordset, you can simply extend the recordset to include the sheet
names and put these in a 2nd variable). Then in your Excel Source
task (inside your dataflow) change the "Data access mode:" drop-down
to "Table name or view name from variable".

Remember that the metadata inside a data flow task must be configured
at design time, so all the sheets you cycle through must contain the
same "table" structure.

Good luck!
J

[PedroCGD]

I made an example for those people that still need:
http://biresort.net/blogs/pedrocgd/a...orksheets.aspx
I hope this help.
regards,
Pedro