SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


stored procedure process with ssis


stored procedure process with ssis

Author
Message
wnarretto
wnarretto
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 85
this post is really meant for ssis 2008 but there is no ssis 2008 spot, just a 2005. Sad 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- Smile
n79799
n79799
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 122
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.
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8643 Visits: 7660
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?


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
wnarretto
wnarretto
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 85
thank you n79799 and Evil Kraig F for your responses with helping me understand my scenario. Smile 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. Sad
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. Smile
thanks again guys for your input. Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search