Solved

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

Posted on 2011-09-29
7
415 Views
Last Modified: 2013-11-10
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!
0
Comment
Question by:sqlcurious
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36819106
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
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36880479
Yes huslayer: is right. but make sure for option one parameters for all sp's are same.



0
 

Author Comment

by:sqlcurious
ID: 36893990
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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 500 total points
ID: 36895119
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
 

Author Comment

by:sqlcurious
ID: 36921067
Great thank you so much! would you be able to share the package here?
0
 
LVL 21

Assisted Solution

by:Jason Yousef, MS
Jason Yousef, MS earned 500 total points
ID: 36921609
Sorry, I thought I've uploaded it... here you go..

http://www.box.net/shared/xcundlkate5ctfqvbdzx
0
 

Author Closing Comment

by:sqlcurious
ID: 36952352
Thanks!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question