Solved

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

Posted on 2011-09-29
7
416 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

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