I m managing a web app featuring countless ETL (Extract, Transform & Load) processes feeding a datawarehouse (using SSIS dtsx packages + *.sql files).
At the moment, everything is governed by several SQL Agent processes monitoring "scheduling queue" tables.
I m trying to figure out a way to develop a scheduler or scheduling "framework" of some sort that needs to do a subset of what SQL Server Agent does (executing dtsx packages, executing SQL) but with a configuration UI based on ASP.NET, as users need to be able to create & modify schedules, manually launch processes and monitor custom logs.
Configuration info, activity, execution logs, schedules, jobs, job steps and everything else needed that I m missing should be persisted to DB.
I m guessing a Windows Service would be flexible but development costs might skyrocket.
I appreciate any sort of input, particularly some clues on SQL Server Agent s internal workflow. I DID try searching for info, but it being PROPRIETARY software, I found zilch.
(maybe I should specify I m more than familiar with SQL Server and C# based .NET development, I just have no idea where to start from..)