October 8, 2012 at 11:57 am
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
wnarretto- 🙂
October 9, 2012 at 12:32 pm
First the answer to your question.....the procs will continue to run (i think) because once the are engaged by the ssis, even if SSIS completes, sprocs will continue.
But i said, 'i think' above because few people would have faced this situation, not because the situation itself (ie identifying recs to be processed, processing them, inserting processing logs in a control table, writing a file after that ) is unique but because you have tried it in a unique manner. It seems you using SSIS ONLY for the 'loop'. You could easily have a outer proc calling all the 4 procs in a loop in sql also without involving SSIS.
I would suggest a diff architecture.
Do the 'sproc 1 ' in SSIS using conditional transform.
Then call 'sproc2' in an execute sql task.
Then maybe do the sproc3 part also in a dataflow task.
Then push the data to a file by using available tasks(bulk insert, dataflow, lookups etc) etc to generate the file.
Chain the above as a dataflow inside the ssis.
Hope this helps.
October 9, 2012 at 2:05 pm
wnarretto (10/8/2012)
1. when the ssis package has completed, does it cut off the connection to the spocs that were called for the other iterations?
Unless you're preserving your connection in the data source, those connections are severed on each object as it completes.
The problem you're running into, as you've seen, is your external call to a separate application. That app call returns as 'successful' immediately. I believe there's a switch where you can have BCP send back success/fail, but really... why are you calling BCP at all? You're IN SSIS already, why not just use a dataflow?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 9, 2012 at 4:30 pm
thank you n79799 and Evil Kraig F for your responses with helping me understand my scenario. 🙂 i have made some changes to the sprocs and now the process works as intended. they use sprocs on everything along with .bat files. apparently my boss is from the old school where sprocs were used all of the time. 🙁
i will say that both of you are correct in the fact that i should be using a different architecture but sadly my boss wants it this way.
i am not happy with the way they do the archaic processing so i have built a couple of packages to do the same job but with a lot of changes that basically takes the ororiginalrocessing time of 6 to 9 hours and reduced it to 2 hours. also i added some error handling, logging and notification which in this case is way better than what they are doing now.
i hope he will be able to see the difference and realize that my way is better. 🙂
thanks again guys for your input. 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply