We have one SQL Server 2008 on which we run several ETL packages in parallel. This setup has always suffered stability issues.
Since I have taken over I am trying to find a way to increase stability so my team focus on development instead of maintenance.
At the moment the SSIS ETL packages are scheduled using the task scheduler and fine tuning the starting time is how they fix stability issues. When debugging using 1204 error flag we noticed a considerable amount of deadlocks, which are probably the cause of the issues. But the amount of queries to debug and eventually rewrite is a giangantic task.
The ETL duration is about 8h, with some packages sometime overlapping each other.
My question here is to know if using a broker such as Service Broker, or any message broker, instead of the task scheduler, would avoid overlapping, therefore avoid deadlocks, therefore increase stability ?