Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

Calling a stored procedure in SSIS for each record

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
shah36
Asked:
shah36
1 Solution
 
Arifhusen AnsariBusiness Intelligence AnalystCommented:
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
 
shah36Author Commented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now