Saving multiple resultsets to flat file in one execute sql command

[WalterHeisenberg]

Hello,

I have a stored procedure which is returns 10 resultsets from 10 queries. I am using BCP/xp_cmdshell to save this to a flat file which then gets emailed out each morning. I wanted to do this without using bcp/xp_cmdshell but could not think of a way to do this in SSIS without having 10 separate data flow tasks and flat files (one for each query). I am a total newbie to SSIS so I apologize if this is a very easy question. Can anyone help?

 

[jjefferson]

Is the layout of the 10 result sets identical, just coming from different queries? Or is the structure of each dataset different?

Some random thoughts with the info we have so far:

- You only have to define the 10 data flows once time. Tedious at first but once done you don't have to worry about it any more. A script task could then join the output files together.

- Can the stored procedure build a temp table or table variable from the results of the 10 queries, and output that as a result set for the SSIS package to process?

Hope this gives you some ideas, or perhaps you could supply a little additional detail about the nature of the result sets and the final output you want to arrive at to be emailed.

-Jim-

 

[WalterHeisenberg]

Hi Jim,

Thanks for taking the time to respond. The data structure of each result set is different unfortunately. I might go the temp table route as that seems to be pretty easy to implement. I was hoping there was some way to do this with a for/each loop but whatever produces the right result will work.