SSRS Data Driven Subscriptions to Support Alarmistic
Here’s the scenario… My company is still working with a SQL Server 2000 based process. While in 2011, there will be major changes to the processes that will involve Oracle and newer MS SQL Server technologies, the fact of the matter is, today, there exists a database structure and poor applications logic that allow for bad data to occur. Here is my example in general terms, plus the use of Data Driven Subscriptions used to automate monitoring these issues.
Daily (well, actually nightly), we have new items to bill, moving from database A to database B. The original process is simply several T-SQL Stored Procedure (SP) calls to move data from one database to staging tables on another database, and then perform further SP calls to manipulate data, and then finally move from staging tables into target tables, again via SP calls. (Yes, SSIS should have been used, but this existed way before I arrived on the scene.) Well, there is a date stamp associated with the records being moved reflecting the date of the move. The problem is, for some reason yet-to-be-determined, the date stamp sometimes comes over as NULL. There are defaults in the table to add the GETDATE() value, but still, the date stamp disappears! The disappearance happens maybe once every month or so across thousands of records, so the occurrence is rare to say the least. The thing is, downstream processes will break if the date stamp field is not populated. So to manage this, we actually had an IT resource each morning run a SELECT script to identify any NULL date values. Can you imagine, coming into the office, opening SQL Server Management Studio (SSMS), and running this script daily, only to hope no records are returned? Wow, right? If there are records found, then appropriate action is then taken.
For a Data Driven Subscription in SSRS (2005, specifically), I simply created a nice report in SSRS to monitor what records match the NULL date state. Mind you, running the report daily in SSRS to hope nothing appears is as bad as running a SQL script every day in SSMS. Also, scheduling a daily report via normal Subscriptions is similarly bad. So instead, I created a Data Driven Subscription. The key to the Data Driven Subscription is how I populated the recipients of the subscription. I used a script similar to the following:
SELECT TOP 1 [Recipient1] = ‘PersonToAlert@MyCompany.com’
FROM TargetTable WITH (NOLOCK)
WHERE TargetDate IS NULL
If there is even one record pulled from this query, then the Data Drive Subscription fires an EMAIL to the recipient (or more if you choose to add EMAIL addresses delimited by semi-colons, although we send the EMAIL to a Outlook email group). This way, instead of running a daily query or report, the report is generated and sent only when the scenario occurs.
Do you find yourself in this scenario? I know in my company, I have employed this a few more times to watch and monitor data. I am sure there are more robust ways to monitor data. Heck, we should have solved the issue outright by figuring out what went wrong. But, we do know our processes are changing, so this problem will go away sooner than later, and creating a Data Driven Subscription is quick and easy! Plus, I got kudos for removing daily tasks from IT resource, so yay for me!!! heheheh… Please keep in mind, though, not all versions of SQL Server 2005 and up has Data Driven Subscriptions, so check please check your documentation. More information on Data Driven Subscriptions can be found here on MSDN.
So what do you think? Automation, right? Anything where a user, developer, IT resource or such, does a regular task should be automated, right? That was the opportunity I saw, and with SSRS, I was able to solve and implement with east!
Please let me know if you have any questions. Thanks for reading! And see you next month for T-SQL Tuesday!