Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-09-29
7
Medium Priority
?
420 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 2000 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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

610 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