SSIS Result Set and Stored Procedure
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.
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
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.
Mike
Mike
hth
Mike