SQL Server Integration Services Automation

Automation Process

I have created five SSIS package for five source systems. I have scheduled the SSIS package as a job using SQL Server Agent. The jobs have been scheduled to run on every hour on weekdays. You could change the frequency interval at your convenience. The primary (Control Report) job has been enabled by default and rest of jobs is not enabled & rename with suffix as "-- Disabled".

The enabled job would start execute, calculate & set the schedule start date for the subsequent month, rename (suffix as "-- Disabled") & disable the job on success. Rename (remove suffix as "-- Disabled") & enable the next job for execution. The cycle spins for other jobs until the final job for a month.





Let us read further to find out how the "Thought Process" has been executed in SQL Server 2005 using integration services.