Planifier des packages SSIS avec l’Agent SQL Server

Planifier et exécuter des packages SSIS automatiquement avec l’Agent SQL Server.

Pour exécuter, planifier ou ordonnancer des packages SSIS directement depuis SSMS, il suffit d’utiliser l’Agent SQL Server. Savoir gérer l’exécution des packages SSIS est important pour un projet Microsoft Business Intelligence. Surtout lorsque le projet a une dimension globale et des interactions avec d’autres travaux ETL. En effet, l’industrialisation des processus de chargements ETL fait partie intégrante d’un projet décisionnel. Planifier et ordonnancer les packages Integration Services est possible depuis SSMS sans utiliser de logiciels tiers.

Cela permet de planifier et automatiser l’exécution des packages SSIS et de les gérer l’enchaînement de manière automatique. Suivre les étapes ci-dessous pour apprendre à planifier des packages SSIS depuis l’Agent SQL Server et avec un proxy Integration Services. Voici comment créer, configurer et planifier le travail depuis l’Agent SQL Server avec SSMS.

1. Créer un nouveau job avec SSMS et l’Agent SQL Server

Ouvrir SQL Server Management Studio et se connecter à une instance. Depuis l’explorateur d’objets, ouvrir le dossier SQL Server Agent, faire un clic droit sur Jobs puis choisir New Job.

Dans la fenêtre de dialogue New Job de SQL Server Management Studio:

1. Entrer un nom explicite pour le travail à planifier: Import Customers
2. Activer ou non le travail en fonction des besoins.

2. Ajouter une étape au travail SQL Server

Dans cette deuxième section, ajouter une nouvelle étape de type package SSIS au travail.

Dans l’exemple ci-dessous le travail exécute un package SSIS situé sur le disque d’un PC Windows. Il est également possible d’exécuter des packages déployés sur une base de données SQL Server ou directement depuis le magasin de packages SSIS. Configurer l’étape comme suit pour exécuter le package SSIS:

1. Entrer le nom de l’étape : Run PackageImportFlatFile.dtsx
2. Choisir le type de tâche : SQL Server Integration Services
3. Sélectionner la source du package : File System
4. Enfin renseigner l’emplacement du ficher DTSX : C:\ssis\PackageImportFlatFile.dtsx

Noter que le paramètre Exécuter en tant que, ou Run as en Anglais, est renseigné avec le compte de service de l’Agent SQL Server. Il s’agit du réglage par défaut bien que d’un point de vue sécurité, il peut ne pas convenir aux exigences du projet. Dans ce cas, configurer un Proxy SQL Server pour donner une autorisation uniquement pour exécuter un package SSIS. Cette configuration se fait à partir d’une étape d’un job depuis l’agent SQL Server. Il est également possible d’exécuter des packages SSIS depuis une fenêtre MS-DOS avec la commande DTEXEC.

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

3. Planifier le package SSIS et sa récurrence

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.

1. Entrer le nom de la planification : Run every 5 minutes
2. Régler la fréquence et la répétition : Quotidienne et chaque jour
3. Indiquer la récurrence : 5 minutes
4. Renseigner enfin la date de fin de la planification : Pas de date de fin

Exécuter maintenant le travail depuis l’Agent pour vérifier le bon fonctionnement du package et son ordonnancement.

4. Vérifier le service SQL Server Agent via les services Windows

Si le service de l’agent n’est pas démarré ou non accessible, alors cette erreur SQL Server s’affiche:

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)

Pour éviter cette erreur SQL Server en raison, démarrer simplement le service sur le serveur ou le PC Windows depuis la fenêtre de gestion des services.

1. Depuis la barre de recherche Windows, écrire Services
2. Ouvrir puis rechercher les services SQL Server, faire un clic-droit sur le service SQL Server Agent (MSSQLSERVER). Le nom de l’instance sera différent pour une instance nommé SQL Server.
3. Démarrer

5. Exécuter le travail et le package SSIS

Pour exécuter le travail manuellement, faire un clic-droit sur le travail et sélectionner, Démarrer à l’étape.

Une fois le package finit, la fenêtre de statut s’affiche.

6. Vérifier l’historique des packages SSIS exécutés avec l’Agent SQL Server

Pour afficher l’historique et le statut des exécutions des travaux planifiés ou exécutés de façon manuelle depuis l’Agent MS SQL, faire un clic droit sur un job de la liste et sélectionner Voir l’historique.

Conclusion sur la planification SSIS avec l’Agent SQL Server

Enfin, l’agent SQL Server offre une multitude d’options pour planifier et ordonnancer des packages SSIS. En fonction des besoins, activer un proxy et des alertes sont utiles. Voici enfin comment déployer un package SSIS en ligne de commande MS-DOS.

Déployer un package SSIS en cmd avec la commande dtutil