To create ssis package to run stored procedures one after another and save result to a file

Hi experts I have a situation where I was asked to run few stored procedures one after another and save their result in an seperate excel files.
The names of the stored procedures are of similar to fowllowing:
orderform_001, orderform_002, orderform_003.
Please suggest how I would be able to achieve this? I am assuming I will an need an dataflow task with oledb source and excel destination - this dataflow task should embed within a forloop, this is how I am visualising it, please help me with this.
Thanks!
sqlcuriousAsked:
Who is Participating?
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Hi,
I meant if they're the same in structure, so you can use the same data flow task, and just pass the name of the SP.

Excel is picky and annoying and you need to set the columns prior !!

Anyway to increment, You have 2 options,

1-using foreachloop container

A-execute sql task to get the last of the stored procedures in the DB
B-Pass it to a Variable of OBJECT type
C-foreachloop to shred the OBJECT variable to another variable to hold the SP name
D-pass it to a data flow task and so on...


2-using forloop container
A-1 variable has the name
B-script task to incrment the last number
C-forloop to loop till the desired number

see attached....

huslayer-508462.flv
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
You have 2 options... if all the SP identical and the excel files will be identical..

Just add a foreachloop to loop over the SP, and inside the foreachloop container add the data flow, which will have the source and excel destination..

Let the foreachloop loop over your stored procedures and pass their names to the variable , which you'll use in the data flow, in the source.

2nd option, if it's different, then you'll have to create separate data flows for each excel file and stored procedure and connect all of them together or parallel.


so what;s ur situation? and which part you need help with?

0
 
Alpesh PatelAssistant ConsultantCommented:
Yes huslayer: is right. but make sure for option one parameters for all sp's are same.



0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sqlcuriousAuthor Commented:
Hi Huslayer, thanks for your answer, I didnot quite understand when you meant that all the SP's are same....they are different SP's with names as below:
orderform_001, orderform_002, orderform_003 etc
When we pass their names as variables, do we increment the number so that it will automatically execute the second proc after first in the foreachloop?
This is where my confusion is, how would I accomplish this.
I think my problem is using the foreachloop and assigning the variable to it, so that the nuumber gets incremented, am not sure how to implement this, thanks!
0
 
sqlcuriousAuthor Commented:
Great thank you so much! would you be able to share the package here?
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Sorry, I thought I've uploaded it... here you go..

http://www.box.net/shared/xcundlkate5ctfqvbdzx
0
 
sqlcuriousAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.