SQL Server provides you the capability of scheduling when a package is going to run.
In SQL Server 2022, the primary tools used to schedule the execution of SQL Server Integration Services (SSIS) packages are:
✅ 1. SQL Server Agent
SQL Server Agent is the most common and recommended tool for scheduling SSIS packages. It allows you to:
- Create jobs with multiple steps
- Add a step that runs an SSIS package
- Schedule the job to run on a recurring basis (e.g., daily, weekly)
- Monitor job history and execution status
How to schedule a package using SQL Server Agent:
- Open SQL Server Management Studio (SSMS)
- Navigate to SQL Server Agent > Jobs
- Create a new job
- Add a job step of type "SQL Server Integration Services Package"
- Select the SSIS package from the SSISDB Catalog or file system
- Define the schedule under the “Schedules” page
- Save and enable the job
✅ 2. SQL Server Integration Services Catalog (SSISDB) + SQL Agent
When packages are deployed to the "SSISDB catalog", you can:
- Schedule them through SQL Server Agent with better logging and parameters
- Use Integration Services Dashboard in SSMS to monitor execution
✅ 3. PowerShell + Task Scheduler (Alternative)
You can use `PowerShell` scripts to execute SSIS packages via `dtexec` and schedule them using Windows Task Scheduler.
Start-Process "dtexec" -ArgumentList '/f "C:\Packages\MyPackage.dtsx"'
This is useful when SQL Server Agent is not available, such as in SQL Server Express Edition.
✅ 4. Azure Data Factory (for Hybrid/Cloud Scenarios)
If your SSIS packages are lifted to Azure, Azure Data Factory pipelines can orchestrate and schedule SSIS package execution in SSIS Integration Runtime.
Summary Table
Tool |
Use Case |
SQL Server Agent |
Primary scheduling method |
SSISDB + SQL Agent |
Managed deployment and scheduling |
Task Scheduler + PowerShell |
Lightweight or Express deployments |
Azure Data Factory |
Cloud-integrated SSIS scheduling |
Let me know if you want a script to schedule an SSIS package with SQL Agent.
The following procedure provides steps to automate the execution of a package by using a SQL Server Agent job step to run the package.
To automate package execution by using SQL Server Agent
- In SQL Server Management Studio, connect to the instance of SQL Server on which you want to create a job, or the instance that contains the job to which you want to add a step.
- Expand the SQL Server Agent node in Object Explorer and perform one of the following tasks:
- To create a new job, right-click Jobs and then click New Job.
- To add a step to an existing job, expand Jobs, right-click the job, and then click Properties.
- On the General page, if you are creating a new job, provide a job name, select an owner and job category, and, optionally, provide a job description.
- To make the job available for scheduling, select Enabled.
- To create a job step for the package you want to schedule, click Steps, and then click New.
- Select Integration Services Package for the job step type.
- In the Run as list, select SQL Server Agent Service Account or select a proxy account that has the credentials that the job step will use.
For information about creating a proxy account, see Create a SQL Server Agent Proxy.
Using a proxy account instead of the SQL Server Agent Service Account may resolve common issues that can occur when executing a package using the SQL Server Agent.
The next lesson will introduce you to the important features of the