troubleshooting Question

SSIS Result Set and Stored Procedure

Avatar of barkome
barkomeFlag for Canada asked on
SSIS
5 Comments1 Solution272 ViewsLast Modified:
Hello

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
A,2,3
B,3,4
C,5,6

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.
ASKER CERTIFIED SOLUTION
DcpKing

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros