this post is really meant for ssis 2008 but there is no ssis 2008 spot, just a 2005.
so pleas forgive this misplacement.
i am trying to figure out how multiple stored procs function within a for loop in ssis.
here is the scenario....
my ssis package executes multiple stored procs in sequence for 3 test clients.
sproc 1. determines the client info that need to be processed then passes the info to sproc 2.
sproc 2. creates an initial record within the control table to store data on the preprocessing of the clients data.
sproc 3. calls 1 other sproc and 2 functions for preprocessing and logging.
sproc 4. pulls data and then inserts into a table, executes a bcp command that pushes that data into a flat file. last part of the sproc updates post processing values within the control table.
my package uses a for loop container to execute each sproc in succession then go through multiple iterations until all clients have been processed. when the package goes through its first iteration, all sprcs get executed correctly. the package will show green on each task. now the package will start its 2nd iteration and complete. this works fine and will complete for each iteration. also to point out that for each iteration the sprocs that are called are generic sprocs which use variables.
now even though iteration 1 is showing that it has completed and the ssis package has moved on to the next iteration, in actuality iteration 1 is still running and trying to finish up with pulling, pushing and bcp'ing the data. when iteration 3 has completed and all tasks are green, the package is done. mind you itertion 1 is still processing.
in my control table i can see that the record for iteration 1 has all of the pre and post values as well as the data being in the flat file. the record for iteration 2 has all of the pre processing values and no post processing values but the data has been extracted but not bcp'ed into its flat file. iteration 3 is just like iteration 2 in the sence no bcp'ing of the data and no post processing values were inserted into the control table. the bcp'ing of the data and update the control table is done by calling a sproc within sproc 4.
now here is where my question come into play.
1. when the ssis package has completed, does it cut off the connection to the spocs that were called for the other iterations?
Example: sproc 4 is bcp'ing data into a flat file for iteration 1. sproc 3 is inserting values into the control table for interation 2. the package completes for iteration 3 and is showing all tasks to be green. when sproc 4 and sproc 3 finally complete thier steps that they are on, will sproc 3 move on to sproc 4 or stop at its last step due to the package being fully completed?
thanks in advance