October 30, 2010 at 2:19 pm
We are using SQL2008 R2.
We need to call an INTERNAL WCF .NET service whenever new rows are inserted into our RawData table.
We have considered the following two solutions, but are unsure which is the best.
1) Create a trigger on our RawData table that fires a sproc. The sproc would call a .NET CLR that would call into our webservice.
2) Create an SSIS package that would call into our webservice whenever a new row is inserted into our RawData table.
We think option 1 will impact on the performance of SQL, because it will have to wait for the webservice call to complete. This will tie up SQL's resources.
We are unsure how SSIS handles webservice calls, so any advice on performance issues would be appreciated.
Another question..... How is an action triggered in SSIS whenever a new row is inserted?
Any help appreciated.
Regards,
Paul.
November 1, 2010 at 12:31 pm
Option 1 is the most direct way to do it.
However, you can use the SSIS option if you prefer. My understanding is that SSIS packages would need to be called on a scheduled basis using a SQL Agent job.
I have no idea whether SSIS can be called using an insert trigger on a table. Any online references to triggers and SSIS that I can find deal with whether existing triggers are active when SSIS accesses a table with a trigger.
November 1, 2010 at 1:12 pm
There's a third option that may may your life easier for concurrency, but will add significant complexity if you're unfamiliar with it.
Use Service Broker.
The basic layout would be this. Use a trigger to drop a row into a local queue in the database. Once the queue accepts it (very quick), you're good to go from the transaction's point of view. Your user is off and running.
Now, inside service broker, you'll have both your send and recieve queue in the same db. On the receive queue, have it automatically fire off your webservice call procedure on receipt of message, and then send a complete back to the source queue. This will give you single database point in time coverage (the service broker is backed up/logged with the db), and give you asynchronous concurrency with the webservice.
The best part of this is if the webservice is unavailable for some reason, nothing else gets screwed up and you don't lose any calls to the webservice when it becomes available again.
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply