INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Saving multiple resultsets to flat file in one execute sql command

Saving multiple resultsets to flat file in one execute sql command

(OP)
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?

RE: Saving multiple resultsets to flat file in one execute sql command

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-

RE: Saving multiple resultsets to flat file in one execute sql command

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close