Solved

Calling a stored procedure in SSIS for each record

Posted on 2016-09-23
2
61 Views
Last Modified: 2016-09-27
Hi Guys,

I want to execute a stored procedure for each record in the source system.
should i use script component or any other transformation / control?

Please note that my stored procedure has got parameters. That means that i would need to pass some of the columns from source as parameters.

regards
0
Comment
Question by:shah36
2 Comments
 
LVL 12

Accepted Solution

by:
Arifhusen Ansari earned 500 total points
ID: 41812400
Use the Execute sql task to get records from the source system. may be from table.

Store the output of execute sql task in Record set.

Use for each loop container and iterate that record set.

You can get the data from each row of the record and values can be assigned to variables.

You have to use other execute sql task in for each loop container and execute the stored procedure from that Execute sql task. You can use that variable to pass the value in stored procedure as parameter.

use below screen to configure ADO Enumerator.

2016-09-23_18-42-49.png
Refer below screen to assign the variable2016-09-23_18-43-15.png value.
0
 

Author Closing Comment

by:shah36
ID: 41817403
Thank you very much. It works however i have found that the OLEDB Command control does exactly the same  and that is quite easy to use and requires no code.

regards
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSIS FTP Task results 13 83
Export only rows with a specific value from a column is SSIS 1 126
Get Sheetname from Excel Source using SSIS 3 36
Importing Excel Data Into MSSQL 15 54
This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

813 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now