Welcome to dbForumz.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

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

 
   Database Forums (Home) -> DTS RSS
Next:  memory usage or lack of  
Author Message
Conan Kelly

External


Since: Jan 06, 2007
Posts: 24



(Msg. 1) Posted: Thu Nov 08, 2007 9:10 pm
Post subject: SQL 2005 SSIS: Looping through Excel Files and then looping through sheets in those files
Archived from groups: microsoft>public>sqlserver>dts (more info?)

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

 >> Stay informed about: SQL 2005 SSIS: Looping through Excel Files and then loopin.. 
Back to top
Login to vote
jhofmeyr

External


Since: Jul 10, 2007
Posts: 42



(Msg. 2) Posted: Thu Nov 08, 2007 9:10 pm
Post subject: Re: SQL 2005 SSIS: Looping through Excel Files and then looping through sheets in those files [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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

 >> Stay informed about: SQL 2005 SSIS: Looping through Excel Files and then loopin.. 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
Looping Excel Files in Transaction - Hi, I need to upload data from multiple excel files and in each files there are bound to be more than one worksheet. I came across a very good sample on how to achieve this at sqldts.com. I have set up an individual DTS for uploading of each excel file...

sql 2005 ssis- excel connection error - Hi, Using sql server 2005 ssis package. I am looping through excel files to import data into a sql server table. Added a filename variable. This variable gets set inside a foreach loop container. In the Dataflow tab, there is a; excel source oledb sourc...

SSIS: Looping through each sheet in an XL file - Hello all, I need to loop through each sheet/named range (preferably named range) in each XL file in a given folder. I've already created successful "for each file in folder" loops, now I need to figure out how to nest a "for each name...

SSIS: Output XML Columns to XML Files - How can I create XML files from XML columns in SQL Server 2005 Integrated Services? Should I be using XML and ForEachLoop tasks? Please help! -- SSIS: Output XML Columns to XML Files

Excel manipulation in SSIS - Hello, I am new to SSIS, any help is greatly appreciated. I need to convert a few DTS packages that manupulate excel files to SSIS. In DTS, I used vbscript task to open excel, insert data from database, format the data, and save the file. How can I d...
   Database Forums (Home) -> DTS All times are: Pacific Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]