Solved

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

Posted on 2011-09-29
7
413 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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Get the latest status 8 32
Querying data from 3 SQL tables 2 32
Sql server get data from a usp to use in a usp 5 16
Help in Bulk Insert 9 35
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

803 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