Schedule SSIS packages using the SQL Server Agent

Schedule and run SSIS packages automatically with the SQL Server Agent.

To run, schedule or order SSIS packages directly from SSMS, simply use the SQL Server Agent. Knowing how to manage the execution of SSIS packages is important for a Microsoft Business Intelligence project. Especially when the project has a global dimension and interactions with other ETL jobs. Indeed, the industrialisation of ETL loading processes is an integral part of a BI project. Planning and scheduling of Integration Services packages is possible from within SSMS without the use of third-party software.

This allows you to plan and automate the execution of SSIS packages and manage their sequencing automatically. Follow the steps below to learn how to schedule SSIS packages from the SQL Server Agent and with an Integration Services proxy. Here is how to create, configure and schedule work from SQL Server Agent with SSMS.

Table of Contents

1. Create a new job with the SQL Server Agent

Open SQL Server Management Studio and connect to an instance. From the Object Explorer:

Open the SQL Server Agent folder
Right click on Jobs
And select New Job…

In SSMS, create a New Job with a right click on the Jobs folder — *Open SSMS and create a New Job with a right click on the Jobs folder*

In the SQL Server Management Studio New Job dialog window :

Enter an explicit name for the job to be scheduled, for example : Import Customers.
Enable the job, you can also disable the job if required later.

*Configure the job with the name and options*

2. Add a step to the SQL Server job

In this second section, add a new SSIS package step to the job.

Add a new step for work scheduled by the SQL Server agent

In the example below the job runs an SSIS package located on the disk of a Windows PC. It is also possible to run packages deployed on a SQL Server database or directly from the SSIS package shop. Set up the step as follows to run the SSIS package:

Enter the step name: Run PackageImportFlatFile.dtsx
Choose the type of task: SQL Server Integration Services
Select the package source: File System
Finally, enter the location of the DTSX file: C:\ssis\PackageImportFlatFile.dtsx

*Setting up a step to schedule and run SSIS packages with SQL Server Agent*

Note that the Run as parameter is set with the SQL Server Agent service account. This is the default setting although from a security point of view it may not be suitable for the project requirements. In this case, configure a SQL Server Proxy to give permission only to run an SSIS package. This configuration is done from a job step from the SQL Server Agent. It is also possible to run SSIS packages from the MS-DOS command line with the DTEXEC command.

Exécuter un package SSIS en ligne de commande avec DTExec

3. Planning the SSIS package and its recurrence

L’agent SQL Server permet de planifier l’exécution d’un package SSIS à heure fixe ou à intervalle réguliers. Pour cela, ajouter et configurer une planification comme celle-ci. Elle permet de relancer le package toutes les 5 minutes.

Enter the name of the schedule: Run every 5 minutes
Set the frequency and repetition: Daily and every day
Enter the recurrence: 5 minutes
Finally, enter the end date of the schedule: No end date

*Scheduling SSIS packages with SQL Server Agent*

Now run the job from the Agent to check that the package is working properly and is scheduled.

4. Check the SQL Server Agent service via Windows Services

If the agent service is not started or not accessible, then this SQL Server error is displayed:

Start failed for Job ‘Import Customers’.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
SQLServerAgent is not currently running so it cannot be notified of this action. (Microsoft SQL Server, Error: 22022)

To avoid this SQL Server error, simply start the service on the server or Windows PC from the service management window.

From the Windows search bar, type Services
Open and search for SQL Server services, right-click on the service called SQL Server Agent (MSSQLSERVER). The instance name will be different for an instance named SQL Server.
Select Start

*Start the SQL Server Agent service from Windows Services*

5. Run the job and the SSIS package

To run the job manually, follow these 2 steps:

Right-click on the job to run
Select, Start Job at Step…

*Run an SSIS package manually with the SQL Server Agent*

Once the package is finished, the status window is displayed.

Successful execution of the job and the SSIS package

6. Check the SSIS package execution history from the SQL Server Agent

To view the history and execution status of jobs scheduled or manually executed from the MS SQL Agent, right click on a job in the list and select View History. From the Log File Viewer window, you can check multiple elements:

The summary and the status of the job execution, here it is successful.
And the detailed log of the selected row in the lower section.

*SSIS Job History of executed by the SQL Server Agent*

Conclusion on SSIS planning with SQL Server Agent

Finally, the SQL Server Agent offers a multitude of options for planning and scheduling SSIS packages. Depending on your needs, enabling a proxy and alerts are useful. Finally, here is how to deploy an SSIS package using the MS-DOS command line.