Coming Soon – Ouvvi for DataSync / ETL

So we have been busy putting a new solution together to better manage Data Sync projects and ETL along with any solution that requires a series of steps to be followed. This could be an STP system, Order Processing, Data Import/Extract or anything really.

The solution involves a Web Front End application for Management and a Windows Service that handles the actual processing.

The system involves creating a project and adding steps to the project which are processed in turn, each step has a run condition which allows for example running a step when the batch fails such as a step to send an email/sms alert.

Projects and Steps are added to a Queue for processing to ensure that the system remains consistent between failures, re-starts etc and can be easily backed up via SQL Server backup. 

Features

• Scale-out across Processes/Servers/Machines for both Web Application and Service processing.
• Resilient against failures via Retry Pattern and Queue System to maintain consistency.
• Redundancy/Failover via running multiple services on separate machines.
• Unified Logging where all messages from all projects/steps are captured into a single logging table
• Logging clean up where expired logs are deleted (could also be archived)
• Queue clean up where failed/unknown state messages are removed after retry count fails.
• Start of Day/End of Day Triggers
• Easily Develop new Step Handlers for integration of any system i.e. Execute SSIS Package, Run SP, BCP, Backup Database, FTP etc.
• Easily Develop new Triggers such as File Drop, Web Service etc.
• RSS Status Reports
• Content Management to enable Project/Step documentation within the console.

Prototype Step Handler

Creating new Steps is as simple as creating a class that derives from StepHandler like so.

public class SqlStepHandler : StepHandler
    {
        public override void Execute()
        {
            LogService.LogEvent(InstanceID, ProjectID, StepIndex, LogSeverity.Information, "SqlStepHandler.Execute", "Start", this.Name);
        }
    }

Screenshot

System Requirements

We have chosen not to use the very latest software to build this solution so that the requirements are pretty minimal and therefore could be installed easily onto existing infrastructure or virtualized with Hyper-V or VMWare.

For the Web Application

Windows Server 2003+, Server 2008, Vista, 7
IIS 6.0 +
.NET Framework 3.5 SP1
SQL Server 2005+ (and Express)

For the Processing Service

Windows Server 2003+, Server 2008, Vista, 7
.NET Framework 2.0+
SQL Server 2005+ (and Express)