SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Appending the results of multiple stored procedures into a text file using SSIS


Appending the results of multiple stored procedures into a text file using SSIS

Author
Message
SqlServerNinja
SqlServerNinja
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 197
Hello everyone.

I have a requirement where I need to get the output resultant set of various stored procedures and
get them out into a single text file.

I currently used a data flow task with the stored procedure as the source and a text file as the destination.

I would like to know

1) how I can do the same for the remaining stored procedures, sending the results out and appending to
the same text file.

2) I would also like to know how I can include the column headers in the text file as currently I do not have
the column headers included in the output text file.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52530 Visits: 21180
Are the output columns always the same, or do they vary by proc?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
SqlServerNinja
SqlServerNinja
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 197
Hi Phil,

The output columns are actually different.
They vary by proc.

Thank you !
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52530 Visits: 21180
Love & Peace (3/25/2013)
Hi Phil,

The output columns are actually different.
They vary by proc.

Thank you !


But you want them all in the same file - presumably, then, with new column headings intermingled with the data at the point where one proc's data finishes and another's starts?

There is no nice way of doing this. Personally, I'd set up five dataflows which write to five separate text files and then have an Execute Process task at the end which does a copy/append of the five text files to a single, new, file.

I don't have SSIS in front of me and cannot remember the answer to your question about column headings - hopefully someone else will chime in.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
SqlServerNinja
SqlServerNinja
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 197
Hi Phil,

Thank you for giving me direction.
I figured out the answer for the second part of the question.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52530 Visits: 21180
Love & Peace (3/26/2013)
Hi Phil,

Thank you for giving me direction.
I figured out the answer for the second part of the question.


Excellent - so everything developed, tested and implemented now? :-)

Or do you have any further questions?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search