SSIS Result Set and Stored Procedure
Posted on 2016-07-22
I have a SSIS SQL Task-1that calls a stored procedure with 3 columns - Name, Number and ID with multiple rows which is then stored in an object variable for another SQL Task-2 to use the ID as an input.
Task 1 Output:
name, number, ID
The foreach loop workflow is:
- Truncate stage table,
- load data for name A,
- stored procedure to get value for name A and load into destination table,
- move data file for name A to archive....
then repeat the process for name B, then name C one after the other..
Does anyone know the best way that I can get SQL Task-2 to use the ID's from Task-1 one by one and not use all of ID's at the same time.
As it is now, task 2 is processing all of the ID's and creating values to the destination table as:
Name, ID, value
A, 3, 300
B, 4, 0
C, 6, 0
Values for Name B and C is appearing 0 Because data for both of them is not loaded because the foreach loop is not repeating the workflow from step 1 for each of them.