DTS Multiple Excel Sheets

[slowmike]

I am having a problem with getting my DTS package to import multiple sheets from an Excel workbook. I have set up a connection for each sheet, so my DTS package works if there are 10 or fewer sheets, but I have no way of knowing if the workbook has more than 10 sheets. Also, if there are fewer than 10 sheets, the rest of the package does not run. I am hoping there is a "slick" way of importing all the sheets in a workbook if the number of sheets is dynamic (maybe some kind of loop function?). Thanks in advance for your help.

 

[Sweenster]

Check this out for looping in DTS

http://www.sqldts.com/default.aspx?6,103,246,0,1

There is a good sample at very bottom.

 

[MacDaddyNickP]

I am currently learning DTS and have been building a package which is supposed to take a result set, comprising a column of numbers corresponding to a list of desired outputs. The purpose of this package is to generate multiple Excel sheets, the contents of each being the result of a Stored Procedure with a single input parameter. I have successfully generated a result-set global variable, which contains the numbers that are to be used as input parameter for the SP. What I am trying to do is loop through this result set, execute the stored procedure by passing the current value of the Global Variable to it, and then exporting the resulting data generated thereby to a date stamped .xls file. I am having some difficulty with the looping part of this gig. (Getting a bit loopy myself)! I am using Execute SQL and ActiveX scripts, but I still don't quite understand the syntax or the logic of where control structures need to go. I've searched BOL without success. And I've been to some DTS sights. ANY HELP WOULD BE GREATLY APPRECIATED!