Need to execute 39 (as of today) Stored procedures and output each to a pipe delimited file

Question

Need to execute 39 (as of today) Stored procedures and output each to a pipe delimited file

Phillip.Putzback

Ten Centuries

Points: 1251
More actions
December 4, 2013 at 12:51 pm

#285735

All the procs work of a Start and End date. I was wondering if anyone had a ingenuous way to possible do this via a for each container as opposed to creating 39 DFTs?
In a previous situation I was given views, so I kept the list of views in a table, looped through each view and put all the columns in one column and as I added them into the one column I plugged in my || pipes in between and then wrote the rows out in the end to a text file. But these Procs are throwing me a curve.
Any ideas?
Thanks,
Phil

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

stan SSC Eights! Points: 980 More actions · Answer 1

can you store the proc names in a table, direct them with exec and params via appropriate query to an ado data set, loop thru the ado data set building a new variable based exec command for each new proc encountered, follow with an exec sql component whose instruction is based on the latter command?

Alternatively, couldnt a cursor or do while loop flip thru the proc list, build a command and execute for each proc encountered?

Phillip.Putzback Ten Centuries Points: 1251 More actions · Answer 2

Are you saying I should still wrap the pipe delimited output handling within a proc. I am unsure about the ADO component use.

stan SSC Eights! Points: 980 More actions · Answer 3

yes, unless I'm missing something. It seems like the pipes (if they really are delimeters) are just part of one big data "column" to your process.

I've used ado data sets to loop thru generated commands in the past. Usually a query has generated the commands which can be sql commands, dos commands etc.

ADO datasets play well with loop containers. You can direct the contents of a table (or query results) to an ado dataset as if its just another destination connector. And then in turn use that ado dataset to easily drive a loop container.

happycat59 One Orange Chip Points: 29389 More actions · Answer 4

You could try a for each loop that uses the list of sprocs as the item to iterate through and then call bulk copy for each of them

Phillip.Putzback Ten Centuries Points: 1251 More actions · Answer 5

from what I am seeing BulkCopy won't work if the procs have temp tables. But I am researching that now. I do have a chunk of procs that don't have temp so it would at least get me started.