I have been trying for a few days to implement a Service Broker implementation to, service the threading framework I built for SSIS.  When you build the threading framework you have a set number of threads running and I have been looking for a way to modify the number of running threads to accommodate server load.  There are many ways to do this, such as have the threads check a table and turn on/off, but I don't care for that method.  The issue I have is you need to have the thread, once it is 'asleep' to wake up every so often and poll the table.  That's a waste of resources in my opinion.  I would prefer to have a callback mechanism where the thread is put to sleep and woken up when it is time to come back online.  To do this I choose Service Broker.  Threads will 'wait' on a queue and wake up when they should or never if they are to be kept offline.  No table polling, no contention, no wasted resources.

I was all Gung-Ho.  I had tested my Service Broker implementation with two procedures to simulate a request to throttle the engines.  I had a slight set-back, but @mrDenny was kind enough to jump in a give me the necessary information that every resource I could find failed to explain.  I was ready to implement my Service Broker trigger based implementation.

Whoa did the frustration ensue.  The concept was simple.  I had a table which contained a listing of what you could request of the framework, which at the moment is a certain number of engines on/off, based upon the number of engines in the framework (can't ask for more than are currently built).  Realizing that the engines could only check the queue once they finished their work, I didn't want to have a person request taking X engines offline and wait N minutes for the request to complete.  I wanted the table to update and return immediately then have the Service Broker asynchronously handle waiting. What followed was a miserable fail.

I had the trigger built exactly like my test procedures, yet every time I did an update to the table, the trigger would hang on sending the message, not on the wait.  I spent a few hours racking my brains trying to understand this.   I was checking queues, I was checking error logs (thanks to@AaronBertrand), but couldn't figure out why.  I hit the web and found there were a few people in the same boat as I was and they had no answers to their questions.  Dig a little deeper and I found the only way to make a trigger asynchronous is do make a CLR trigger.  My next task.

Spent a few more hours pouring through the online msdn library looking for clr information (here is a good starting point http://msdn.microsoft.com/en-us/library/ms131093.aspx) and TADAH!. I created a clr trigger and it works like a charm.  I do an update to the table and I get an instant return, allowing the user to continue on.

I now have to integrate this into threading framework, but that won't be too hard.  The framework uses a stored procedure to dole out the work, so I just have to put the SB code in there, vs having to modify my SSIS package.  If you are unfamiliar with the threading framework I designed for SSIS, you can download the deck here (http://josef-richberg.squarespace.com/downloads/) and a recorded presentation here http://appdev.sqlpass.org/MeetingArchive/tabid/2005/Default.aspx.

I am not sure yet if this will be a blog post (it will be very large) or an article (more to come on that).