Avatar of barkome
Flag for Canada asked on

SSIS Result Set and Stored Procedure


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.

Avatar of undefined
Last Comment

8/22/2022 - Mon

Have you tried arranging things so that Task 1 only emits records for A and B and not C? If you do that, does Task 2 still provide the same results? In that case I'd suggest that your problem is with Task 2 and not your SSIS.



Yeah, I did so...

I have a stored procedure that creates a data set with ID's, which is then sent to a Foreach Loop ADO Enumerator. Within the Foreach Loop are two stored procedures: The first SP, works as expected, but the second one fails, because its receiving just the first ID presented, and not looping through like the first SP.

Below is a image of the flow.

The logic is, 3 ID's are retrieved 1,2 and 3. Task 1 which is the first SP, will execute with ID 1 as an input, and when complete pass that same ID (1) to Task 2 for the second SP to execute with.

Once this loop is complete, the logic/workflow will start again from task 1 with ID 2, and once complete, start again from task 1 again but this time with ID 3.

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

Thanks for this Task 1 works perfectly well when Task 2 is not part of the workflow, loading the right values one after the other.

Did some digging around and noticed that it had to do with the data type. applied the CAST syntax to the affected columns and it suddenly worked as expected, but only for it to duplicate a few records with a default value of 0.00
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

Take a look at my Task 2 and compare it with yours. Is yours getting the data it needs? If not, have some way of recording what it does get (write the values out to a table) and look at them there. Look at the way that I'm splitting out the values that are passed from task to task into variables. Are you trying to keep them in an object or something? Putting them in variables whose value changes each time around the loop is a lot more reliable and easier to debug.