Calling SSIS packages from a Service Broker Queue

I would like to call an SSIS package from a Service Broker Queue.

There is one way that I am aware of -

Using xp_cmdshell from within an activation stored procedure and using DTEXEC.

Is there a more elegant way of executing an SSIS package from within SSB?

Also, I am not interested in writing a .NET external activator to process my messages in the queue. I would like this operation to be strictly database oriented. Having said this, I am also trying to avoid triggers processing the messages in the queue.

Thank you!

SQLCLR with unsafe assembly (in order to call Process.Start()) would be slightly better option than xp_cmdshell. Even then, starting external processes from an activated stored procedure is limiting and dangerous. Limiting because you can only start the external application as the service account running sqlservr.exe. Dangerous because if the process fails to start (eg> out of memory), you may orphan messages in the queue since there will be no process to process them.

You might want to look at the External Activator sample (linked from www.sqlservicebroker.com) and adapt it or build something similar.

I don't know what you mean by "triggers processing the messages in the queue". SQL Server 2005 has no triggers for queues.

Rushi

|||

We use this mechanism to farm out data warehouse ETL mechanism and it works fairly well.

Once you start up SSIS, though, your package should be the one that reaches back into the DB and pulls messages off the queue. Make sure to put your transaction logic, error handling, etc. in the SSIS control flow, taking into account what should be done with messages in the context of your package.

There's a ton of SSIS overhead (at least compared with pulling a message off a queue), so if you're getting a fair number of messages, you don't want to start up the engine each time you want to process a message. We opted to call out to dtexec rather than have a separate service running, although I think that's a matter of preference (and your level of comfort/concern of running external processes from SQL vs. having to install/manage other services).

|||

Couldn't you just create a SQLCLR stored procedure to execute the package directly & with full transactional support? I.e. Have an activated stored proc, authored in C#, executing the SSIS package without doing any "shelling out".

Something like the link below implemented as a SQLCLR proc?

http://www.codeproject.com/useritems/CallSSISFromCSharp.asp?df=100&forumid=309846&exp=0&select=1519306