SQL 2005 SSIS: Looping through Excel Files and then loopin..

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