Lesson 8	SQL Server Agent
Objective	Scheduling and running a package with SQL Server Agent

Scheduling a Package with SQL Server Agent

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.

SQL Server Agent job step

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:
1. To create a new job, right-click Jobs and then click New Job.
2. 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