Solved

Calling a stored procedure in SSIS for each record

Posted on 2016-09-23
2
29 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
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Today I will describe a subset of an ETL I made and I think that could be useful for you and it will be useful for me if I receive your feedback.1. The Problem I have customer's data (sent by an application by text files to a specific file system s…
From time to time, for debugging and troubleshooting your flow at run time you’ll need to check if the variable has the correct value or not, there’re several ways to check for the value of the variables inside the flow, You can add Break Points, a …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

763 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

12 Experts available now in Live!

Get 1:1 Help Now