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 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.
2. Add a step to the SQL Server job
In this second section, add a new SSIS package step to the job.
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
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.
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
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
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…
Once the package is finished, the status window is displayed.
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.
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.