La gestion des erreurs dans SSIS n’est pas si simple en effet vous avez un nombre important d’options vous permettant de modifier le comportement de l’outil :

Propriété Delay validation
Propriété Maximum error count
Event Handler OnError
FailPackageOnFailure
FailParentOnFailure
Variable System propagate de l’Event Handler OnError

Pas si simple de réaliser une action simple lorsque l’on a autant de configurations possibles (que l’on retrouve au niveau Package, DFT, Sequence Container …).

Prenons justement l’exemple du chargement de fichiers excel avec une connexion dynamique (Je ne rappelle pas le principe de chargement dynamique de fichier, vous trouverez un article ici) afin d’illustrer l’utilisation de certaines de ces configurations.

J’ai donc un package de cette forme :

1er constat, j’ai des erreurs, et c’est normal car mes connexions au fichier sont dynamiques et utilisent l’expression suivante :

@[User::SourceDirectory] +@[User::SourceFile]

Or si le dossier est bien rempli, le SourceFile lui ne sera rempli que lors de l’exécution, il est pour l’instant vide et donc ma chaîne de connexion ne pointe pas encore vers un chemin valide.

Afin de ne pas valider votre connexion avant l’exécution effective de votre tâche vous devez passer la propriété DelayValidation à True sur votre tâche de flux de données (DFT – Excel File) et sur votre tâche de déplacement de fichier (FT – Archivage).

Les deux messages d’erreur doivent maintenant disparaître :

Attention cela ne veut pas dire que SSIS ne validera plus jamais vos tâches, il le fera juste plus tard, avant l’exécution de celles-ci.

Vous voilà avec un package de chargement de fichier parfaitement opérationnel, mais que se passe t’il si une erreur de conversion de données a lieu lors du chargement, si le fichier est ouvert par un utilisateur, si celui-ci est corrompu ?

Votre package va passer en erreur et s’arrêtera sur le fichier qui cause cette erreur, interrompant au passage tout votre chargement de données. Il faut donc gérer cela, pour ce faire nous avons plusieurs options.

Je vais en documenter deux principales, l’event handler et la sortie d’erreur.

Mettre un Event Handler On Error sur mon flux de données comme ça en cas d’erreur celui-ci sera appelé et je pourrais même loguer l’erreur grâce aux variables ErrorCode et ErrorDescription.

Problème, lorsque le fichier comporte une erreur, je logue en effet dans ma table d’audit qu’une erreur a eu lieu et qu’il s’agit d’un verrouillage sur le fichier par exemple, par contre tout mon package s’arrête en erreur (Fail)

Alors que mon Event Handler s’est bien exécuté et a bien inscrit mon erreur dans ma table.

Et c’est un fonctionnement tout à fait normal car l’Event Handler comme tous les composants par défaut propage l’erreur à son parent.

Pour éviter cela, il est possible de passer la variable système Propagate à False.

Félicitations, vous venez de dire à SSIS que dans cette Event Handler vous ne souhaitez pas laisser s’échapper l’erreur qui viendra donc mourir ici. C’est pas très joli et c’est expliqué en détail dans cet article : http://simonworth.wordpress.com/2009/11/11/ssis-event-handler-variables-propagate/ et dans celui-ci http://www.timmitchell.net/post/2013/08/05/continue-package-execution-after-error-in-ssis/ Attention toutefois comme indiqué sur msdn, « The value of the Propagate variable is disregarded during the validation of the package. »

La solution à base de sortie d’erreur, de MaxErrorCount et de FailParentOnFailure.

Pour bien comprendre le fonctionnement des erreurs il faut savoir distinguer le comptage des erreurs (Error Count) et les erreurs en elles même.

Une erreur du type fichier absent va se propager de notre source de données à notre Data Flow Task puis à notre Boucle puis à notre Package ce qui va stopper l’exécution du package. Et donc en fonction du niveau où vous vous trouvez votre nombre d’erreurs sera différent.

Si je reprends mon cas précédent :

J’ai 4 erreurs remontées par mon DFT celles-ci sont propagées au reste du package.

Si maintenant je passe le maxErrorCount de ma boucle à 0 (0 à Nombre d’erreur illimité)

Cela aide en effet ma boucle à ne pas s’occuper des erreurs et donc à charger tous mes fichiers, par contre mon package termine encore en erreur pourquoi ?

Tout simplement parce que les erreurs sont propagées au conteneur parent (le package) qui lui tolère uniquement 1 erreur (Max error count = 1 par défaut) Je peux confirmer ce fonctionnement en regardant le warning qui n’est plus au niveau de mon foreach container mais au niveau de mon package.

Comment faire pour que mon package se termine avec succès ?

Solution 1 : Créer un event handler sur ma boucle en changeant la variable propagate à faux (Attention, cela ne fonctionne pas avec les erreurs de validation)
Solution 2 : mettre le maxerrorcount à 0 sur celui-ci aussi (Mais toutes les erreurs du package seront ignorées).

Dans tous les cas, il faudra s’assurer que les configurations FailPackageOnFailure et FailParentOnFailure sont à False sur les composants qui génèrent l’erreur sinon, peu importe votre gestion du MaxErrorCount, vos tâches vont passer en échec.

J’espère que vous comprenez un peu mieux le fonctionnement des erreurs, à noter que si vous jouez avec ces paramètres je vous conseille très très fortement de gérer vos sorties d’erreur afin de les loguer (Sans cela vous ignorez juste les erreurs et ça c’est mal)

Encore une fois, ceci répond à un besoin très particulier, dans la majorité des cas il est préférable de gérer ces erreurs en amont sous forme de pré-condition (Dans l’exemple du fichier excel ouvert, utiliser une script task pour vérifier si le fichier est bien disponible pour chargement ou copier le fichier ailleurs avant chargement.)