Recently, I came across a need to show an SSIS package execution progress in client .net program. The package is putting the progress info in a log table. So basically client just need to check that log record to get the information. At the beginning, I was using a Timer control in the program to run a SQL query every 10 seconds to get the progress. But because some steps in SSIS package may take less than 10 seconds, and some take more than a few minutes, the program may skip some steps’ update in log table, and can not show every step’s progress to users. So I need a way to monitor the update of that log table. Whenever the log record is updated, I need to show it to the users. The solution I got is SQL Notification Services and SqlDependency.

SQL Notification Services is based on the new features of SQL Server 2005: Service Broker.aspx) and Query Notification.

First, we have to enable Service Broker for our database. Use this query to check Service Broker for database:

SELECT is_broker_enabled FROM sys.databases WHERE name LIKE N’myDB’
>
>

If returning 1, then means it is enabled. Otherwise, we have to run following query to enable it.

ALTER DATABASE myDB SET ENABLE_BROKER
>
>

Then write some codes in client program using ADO.NET 2.0 SqlDependency to get notified whenever log table gets updated. Here is the detail document for how to use Query Notifications. In System.Data.SqlClient namespace: SqlDependency class provides the simplest way to use but without much of customizations. And SqlNotificationRequest provides you more options of customizations. In ASP.NET 2.0, there is a SqlCacheDependency helper class, too. You can find a good sample here. And here has some codes if you want to use SqlDependency with LINQ.