Optimiser un package SSIS : bonnes pratiques de conception et performance

Écrit par Myriam B.

Un package SSIS performant commence par une conception claire, dynamique et modulaire. Suivre quelques bonnes pratiques dès le départ facilite la maintenance, l’adaptabilité entre environnements et améliore les performances.

Voici les meilleures pratiques SSIS à adopter pour rendre vos packages robustes, flexibles et rapides.

Rendez vos packages dynamiques grâce aux variables

Évitez les valeurs codées en dur (chemins, noms de fichiers, chaînes de connexion). Utilisez des variables SSIS pour :

• Centraliser la configuration ;
• Adapter facilement le package aux environnements (DEV, RECETTE, PROD) ;
• Limiter les erreurs.

Exemple simple :

Au lieu d’écrire directement D:\\Data\\Prod\\mon_fichier.csv dans une tâche, créez une variable User::CheminFichier contenant uniquement le dossier D:\\Data\\Prod\\. Puis, dans la propriété ConnectionString du gestionnaire de connexion ou de la tâche, construisez le chemin complet en utilisant une expression SSIS.

Cela vous permet de modifier le chemin à un seul endroit (la variable) sans changer toutes les tâches.

Maîtrisez l’usage des variables, paramètres et expressions

Variables vs Paramètres

• Variables : données internes au package qui peuvent changer pendant son exécution
• Paramètres (de projet ou de package) :
  • Ce sont des valeurs fixes au moment du lancement du package (immutables durant l’exécution).
  • Ils servent à configurer le package au démarrage, par exemple un chemin d’accès, une chaîne de connexion, etc.
  • Les paramètres de projet sont accessibles par tous les packages du projet (partage global)

Expressions : formules dynamiques qui utilisent variables et paramètres pour modifier les propriétés (comme ConnectionString, FileName) à la volée. Exemple d’expression pour une chaîne de connexion :

"Server=" + @[User::NomServeur] + ";Database=Ventes;"

Intégrez des scripts (Python, C#, etc.)

Pour des traitements complexes, ou pour optimiser les performances, il peut être plus rapide d’utiliser un script externe plutôt que des composants SSIS classiques.

Exemple d’Execute Process Task avec Python :

1. Créez une variable SSIS User::CheminSource contenant le chemin du fichier CSV.
2. Créez une variable User::TauxAnomalies (type double) pour stocker le résultat.
3. Configurez l’Execute Process Task

• Executable : python.exe
• Arguments : C:\\Scripts\\calcul.py “@[User::CheminSource]”
• Redirigez la sortie standard vers la variable User::TauxAnomalies

1. Dans votre script Python (calcul.py), récupérez le chemin en argument et calculez la statistique

import pandas as pd
import sys

# Récupère le chemin du fichier passé en argument (variable SSIS)
chemin_source = sys.argv[1]

# Lecture du fichier CSV
df = pd.read_csv(chemin_source, sep=';')

# Calcul du taux d’anomalies en pourcentage
taux = df['Anomalie'].sum() / len(df) * 100

# Affiche le résultat
print(taux)

Ainsi, la variable User::TauxAnomalies contient la valeur calculée, utilisable par la suite dans des conditions SSIS.

Gérer dynamiquement les connexions

Pour les fichiers plats ou bases SQL, évitez les chemins ou chaînes de connexion fixes.

Exemple pour un Flat File Connection Manager :

• Créez une variable User::CheminFichierSource avec le chemin du fichier.
• Dans les propriétés du Flat File Connection Manager, utilisez une expression : ConnectionString = @[User::CheminFichierSource]
• Utilisation des connexions en mode projet : Dans SSIS, il est recommandé d’utiliser des connexions au niveau projet (Project Connection Managers). Elles permettent de centraliser la configuration des connexions, qui peuvent être partagées par plusieurs packages.

Optimisez les performances

Exécution parallèle

Pour gagner du temps, divisez vos traitements en plusieurs morceaux indépendants (par exemple par date, par zone géographique ou par type de données) et lancez-les en même temps. Ainsi, plusieurs tâches s’exécutent simultanément, ce qui réduit la durée totale du traitement.

Tâches SQL vs Data Flow

Pour un transfert simple et de faible volume, privilégiez une tâche Execute SQL avec un

INSERT INTO … SELECT. En revanche, pour des volumes importants, un Data Flow avec l’option Fast Load sera généralement plus performant grâce au logging minimal et au contrôle du chargement.

Optimiser côté base de données

Certaines optimisations doivent être faites dans SQL Server lui-même :

• Ajoutez des index ciblés pour les lectures ou jointures fréquentes
• Utilisez des tables de staging sans contraintes pour les chargements massifs
• Envisagez les tables en mémoire pour les données temporaires très sollicité

Scripts vs composants natifs : comparer les performances

Dans certains cas, un script (en Python, C#, batch..etc.) peut remplacer avantageusement une série de composants SSIS, en particulier lorsqu’il s’agit de traitements complexes ou spécifiques. Il est donc pertinent de tester et de comparer les deux approches pour faire le meilleur choix.

Stocker des paramètres dans des tables

Une autre pratique efficace consiste à stocker les paramètres de configuration (chemins de fichiers, dates de traitement, etc.) dans des tables dédiées en base de données. Au début du package, vous pouvez lire ces données et les stocker dans des variables.

Cela permet :

• D’éviter toute modification directe du package
• De conserver une trace des valeurs utilisées
• De faciliter les ajustements en production

Structurez et nommez proprement

• Nommez clairement vos tâches (Chargement_Client, Nettoyage_Temp)
• Utilisez des containers (Sequence Container) pour grouper les traitements
• Activez la journalisation pour suivre l’exécution de vos packages via le catalogue SSISDB lors de leur déploiement. SSIS enregistre automatiquement les événements importants (erreurs, avertissements, début et fin des tâches, etc.). Le logging vous permet ainsi de bénéficier d’un suivi détaillé, essentiel pour diagnostiquer rapidement tout problème.

Le catalogue SSIS (SSISDB) propose un système de journalisation intégré, simple à configurer et à exploiter.

Lancer les packages SSIS depuis SSISDB

Si vos packages sont déployés dans le catalogue, vous pouvez également les exécuter à travers un script T-SQL dans un package SSIS. Cela permet de piloter dynamiquement l’exécution depuis un autre package.

Exemple d’exécution d’un package SSIS déployé dans SSISDB via une tâche Execute Task :

DECLARE @execution_id BIGINT;

-- Création d’une exécution pour le package
EXEC [SSISDB].[catalog].[create_execution]
    @package_name = N'MonPackage.dtsx',
    @execution_id = @execution_id OUTPUT,
    @folder_name = N'MonDossier',
    @project_name = N'MonProjet';

-- Démarrage de l’exécution
EXEC [SSISDB].[catalog].[start_execution] @execution_id;

Planifiez et automatisez avec SQL Server Agent

SQL Server Agent permet de planifier vos packages, gérer les erreurs et relancer automatiquement les traitements.

Pour industrialiser l’exécution des packages, utilisez le SQL Server Agent. Cela vous permet de :

• Planifier les traitements (par heure, jour, etc.)
• Surveiller automatiquement les erreurs
• Relancer facilement un traitement échoué
• Centraliser la gestion de tous vos flux

Conclusion

Des packages SSIS bien conçus sont :

• Dynamiques et configurables
• Faciles à maintenir
• Performants et fiables

Adoptez ces bonnes pratiques pour une automatisation solide et évolutive.