SSIS & Service Broker Notification

[s...@nospamxyz.com]

Gurus - we have an SSIS package which loads data in staging table and
submits the request in ssb queue for validation.
once the validation is complete the notification is received in the
initiator's queue and written to a table.

Is there a way to notify SSIS process that the validation is completed. The
SSIS package do have the ability to scan the database table for status but i
am thinking of a way to notify external application may be like c# or SSIS
about the completion status. I heard of sqldependency class but not sure how
to use it

Highly appreciate any code snippet or sample.

Thx!

[Dan Guzman]

> Is there a way to notify SSIS process that the validation is completed.
> The SSIS package do have the ability to scan the database table for status
> but i am thinking of a way to notify external application may be like c#
> or SSIS about the completion status.

Assuming that the proc that writes to the status table is an initiator queue
activated one, you could instead turn off the activation and have the
application or SSIS package call basically the same proc but with the
desired conversation_handle added to the WHERE clause of the RECEIVE . The
package would then wait until the RECEIVE completes or times out. You could
also execute the RECEIVE immediately after the SEND all within the same proc
so that proc becomes essentially a synchronous blocking request.

> I heard of sqldependency class but not sure how to use it

You could also use SqlDependency to detect changes to the status table. The
way SqlDependency works, you submit a query and subscribe to be notified
when the result changes. Under the covers, SqlDependency uses Service Broker
to deliver the notifications. A gotcha is that the heavily loaded server
might remove the subscription (and notify your app) so you could miss the
update to the status table.

See the Books Online for more information and examples for SqlDependency and
QueryNotifications.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"s...@nospambbb.com" <s...@nospamxyz.com> wrote in message
news:eVX1rCBi...@TK2MSFTNGP05.phx.gbl...

[s...@nospamxyz.com]

Thx a lot again!!!

"Dan Guzman" <guzm...@nospam-online.sbcglobal.net> wrote in message
news:uuMJO8Ti...@TK2MSFTNGP02.phx.gbl...