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. 🙂