January 14, 2010 at 1:29 pm
Hi All, need some help please!!!
I am writing a package to execute an stp that returns multi result sets (it has 4 select statements in it), sometimes all results might be blank or some results may be returned in any one of the resultsets.
(Please note the stp has already been written and cannot be changed)
In the package I need to execute the stp, check if any results are returned, if so then copy each of the 4 result sets into 4 seperate tabs in one excel file, ideally I would also like to create the Excel file on the fly and use the field names that are returned in the stp, the field names for each resultset are also different from one another.
So far I have thought about different possibilities of getting the data out, storing the results in an object variable then iterating through via a foreachloop or via a script component, or just using an oled_db source to retrieve the data into a recordset etc.., but I can't think of how to iterate identifying when the next result begins and of how to also read the field names and how to copy them them all to a not yet created Excel file.
Hope that makes some sense! 🙂
Many thanks for your help in advance!!! 🙂
April 24, 2012 at 8:51 am
I know this post has been up for a while with no response, but I just came across it while searching for something else...
Have you considered doing this with a Reporting Services report that would use the stp as a data source, and group by the result set into separate tabs? That would build the spreadsheet the way you want it done...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply