Tod means Fox

This article is part of a series discussing the Kimball Group’s “34 Subsystems of ETL“. The Subsystems are a group of “Best Practices” for delivering a BI/DW solution. In my articles, I discuss how each Subsystem can be implemented in SSIS or hand coded in Visual FoxPro.

This subsystem addresses one of the more basic management-related functions of ETL: Scheduling. You must provide a way to reliably run your packages and scripts. Data moves into and out of the Data Warehouse continuously; scheduling provides a structured and predictable way of managing the process.

I use the term “predictable” because it is often necessary to provide business users and other applications with data at specific intervals throughout the day and week. In my current project, for example, we provide a reporting data mart to a specific analytical application every morning at 9am. This reporting mart must have data up until the close of the previous day (and is not refreshed throughout the current day). If the timing wasn’t right (if, for example, some of yesterday’s data came into the database at 9:30), then the reporting database would not be ready for the business users. All of these timing issues are solved with a scheduler (and of course some agreements with the data providers to ensure data is available to process by the time the scheduled task runs!).

Scheduling is also used to organize and arrange processes so that dependencies and relationships among disparate data sources are processed logically. If data A must be loaded before data B and C, then data A will be scheduled first (worth mentioning again: a prerequisite for this is that the data is available from the source at the right time as well!). If one load fails, a graceful exit from the process must be made with adequate notifications and alerts. Restarting at failure points (without having to rerun the entire process) is also a highly desireable feature of any scheduler.

I’ll get into these topics more in a few of my next subsystem posts.

SQL Server 2005 Integration Services (SSIS)

Scheduling SSIS packages is easy. The best option you have is also the most intuitive: The SQL Server Agent.

The SQL Server Agent is a Microsoft Windows service that executes scheduled tasks. These tasks are referred to as Jobs. A single job can consist of multiple steps and be run on multiple schedules. Schedules can be configured to respond to a specific event, at intervals, specific times, or on demand. In a single job, for example, you can run a few SSIS packages, some database stored procedures, and a few command line utilities. These can all be scheduled at various times throughout the day (perhaps each hour, or once in the morning and again at lunch), and you can get an email telling you when it finished.

The scheduling aspect of SQL Agent is quite powerful. Not only can you create a seemingly endless combination of schedules, you also have full control over how to handle each step if it fails or succeeds. All of these steps can be linked together in such a way that if any one job step fails, processing can halt or move on to another step (of your choice). If you think about it, you have built-in If/Else/EndIf and CASE logic available right in the scheduler. I find that developers who come from a programming background can make quick sense of this put together some rather elaborate and logical flows, with dependancies, prerequisites, and alternative paths built in.

Knowing this ahead of time can save you some effort in your ETL packages. So here’s a tip: Use the scheduler to manage package dependencies where you can, rather than trying to build these complexities into your Control Flows. Of course, every package should start out by examining the environment, to be sure that it has everything it needs to start processing. This might be the existence of a CSV file in some directory, a flag in a staging table that indicates a previous process has finished, or any other check-able condition. If you can get into the habit of using the scheduler for some dependency management, and you are familiar with using parent/child packages, then you really have full control over your ETL processes.

One drawback to using the scheduler for managing dependencies is that your packages are now dependent on the scheduler! If, for example, you need to debug a package, you may be forced to run other packages first, or you may need to manually create the environment ahead of time (set flags, move files, run other scripts, etc.). This is in contrast to having a single super master package call dozens of other master packages and SQL Tasks in a more visual way. The scheduler in this environment would simply schedule the super master package and that’s all.

Hand Coding with Visual FoxPro (VFP9)

Finding solutions for management-related and process-related development problems in VFP is easy if you utilize the wealth of information and tools available from the FoxPro community. The community has always been one of FoxPro’s greatest assets. And this subsystem, more than any other discussed so far, highlights this.

Scheduling is not something you can do out-of-the-box with VFP. One very good option is to simply use the Windows Task Scheduler to run your compiled executables or application files. If you’ve been following Doug Hennig’s blog, then you might have seen his excellent Task Scheduler class. He writes:

This provides classes that can be used to create tasks in the Windows Task Scheduler. There are separate classes for Windows Vista, which uses Task Scheduler 2.0 and earlier Windows versions, which use Task Scheduler 1.0. Updated 2007/04/26.

TaskScheduler.zip is available for download on Stonefield’s Whitepaper and SourceCode page.

If you’re using SQL Server for your data warehouse database, then you could (and rightfully should) use the SQL Agent to run your VFP ETL applications from the command line (see my previous section on SSIS). In fact, in one system I worked with, I used a combination of SSIS packages with VFP compiled applications to handle a fairly complex VFP/SQL Server data integration environment. I used VFP much like SQL Server would rely on stored procedures and the CLR to conduct some complex row and set-based analytics. The SQL Agent — as with any scheduler — is a great way to bind different technologies together.

An option you should not explore is using the VFP Timer control in a running VFP application to schedule tasks. There are better options out there. If you disagree, feel free to start a discussion in the comments section of this post. I’ve attempted to use the Timer a few times in the past for these sorts of things, only to scrap it and go another way. One area worth exploring, however, is using a Timer in an application designed for the SysTray. Microsoft provided a SysTray class as part of their Solutions Samples when VFP8 was released. But even still, the scheduling capabilities of both SQL Agent and the Window’s Task Scheduler are very good and built in to both Windows and SQL Server environments.

From Here

I’ll continue discussing the management-related ETL Subsystems in my next series of posts. The next one is the Backup System, ETL Subsystem 23. Feel free to pass along any questions in the meantime!