SSIS – Message d’erreur du jour : Insufficent system resources

Le sujet technique du jour c’est SSIS – SQL Server Integration Services, l’ETL de Microsoft inclus dans SQL Server – qui nous le fournit avec le message d’erreur suivant:

SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred.
Error code: 0x80004005. An OLE DB record is available. 
Source: "Microsoft SQL Native Client"  Hresult: 0x80004005 
Description: "Protocol error in TDS stream".  An OLE DB record is available. 
Source: "Microsoft SQL Native Client"  Hresult: 0x80004005 
Description: "Communication link failure".  An OLE DB record is available. 
Source: "Microsoft SQL Native Client"  Hresult: 0x80004005 
Description: "TCP Provider: Insufficient system resources exist to complete the requested service.  ".

Ce message d’erreur provient évidemment de sysdtslog90 (SSIS 2005), que la bienséance nous impose de mettre en œuvre 😉

Quant au contenu, hum…

La stack TCP qui fait des siennes? Ressources systèmes insuffisantes? C’est vrai que la machine est un peu au tacquet, mais quand même! Regardons le package qui a généré l’erreur:

20120123 SSIS : Avant

C’est propre, c’est bien rangé. Si on zoome dans les flux de données rien de bien méchant: on passe de notre base de staging à notre base de staging en faisant quelques opérations de nettoyage de données:

20120123 SSIS DataFlow

De nouveau c’est propre, rien de particulier. En soit le package n’est pas vraiment lourd. Certes. Mais on l’a vu dans le Control Flow, c’est 10 flux de données comme celui-ci qui vont s’exécuter en parallèle. En terme de buffers et de connexions à la base, c’est certainement là où on atteint les limites de la machine.

Et en effet, il suffit de repasser en séquentiel pour que tout aille mieux:

20120123 SSIS Apres

On passe d’un échec critique à une exécution en moins de 2 minutes 🙂 (Hola pas si vite malheureux, cf mise à jour en bas de l’article)

A retenir: par défaut, enchainez vos dataflows les uns après les autres, laissez SSIS gérer la parallélisation des flux ne vous préoccupez pas de la parallélisation!

Évidemment comme toute règle elle comporte des exceptions et une des premières se situe au niveau de la phase d’extraction (la collecte). En effet vous pouvez aller lire toutes vos données sources en même temps puisque dans ce cas, en général ce sont les bases distantes et/ou la connexion réseau qui sont les facteurs limitant, pas votre serveur (enfin restez raisonnables quand même ;))

Concernant les messages d’erreur en eux-même: « Protocol error in TDS stream » et « TCP Provider: Insufficient system resources exist to complete the requested service« , ce qui est certain c’est qu’ils ne sont pas tellement explicites. C’est un fait, avec SSIS il faut apprendre à interpréter ces messages et là pas de secrets : cela vient avec l’expérience.

Enfin, si vous voulez creuser la parallélisation des flux, et plus globalement comment SSIS fonctionne sous le capot, je vous laisse consulter ces articles: le MSDN sur SSIS 2005, Arshad Ali sur SSIS 2008 et Todd McDermid qui fait le tour sur le parallélisme dans SSIS. Bonne lecture 🙂

Mise à jour 24/01/2012 : On m’indique dans l’oreillette que nos problèmes ne sont pas complètement réglés! Le fait de sérialiser les flux de données a certes allégé la pression sur le serveur, mais il continue tout de même de renvoyer de manière intermittente le même message d’erreur sur certains packages. Il s’agirait au final d’un bug « connu » du SP2 de Windows Server 2003. Le remède, cette ligne de commande : « Netsh int ip set chimney DISABLED ». Je mettrai à jour l’article si on trouve autre chose (et par on je veux dire David 🙂 )

Mise à jour 31/01/2012 : Problème résolu! Solution = Sérialisation + Chimney Disabled

Parallélisme et Lookups dans SSIS

Todd McDermid a publié un très bon article il y a 15 jours sur le parallélisme et les lookups dans SSIS.

Son approche est excellente, ça vaut le détour, mais pour ceux qui n’ont pas le temps je vous le résume en 2 points:

  • L’interface graphique de SSIS nous ment très régulièrement. C’est une abstraction qui ne représente pas exactement ce qui se passe réellement sous le capot.
  • Conclusion: comme pour SQL Server et le T-SQL, dans 95% des cas il vaut mieux laisser SSIS optimiser tout seul le data-flow plutôt que déplacer les blocs dans tous les sens.

Nb: Je passe l’air de rien un lien vers la loi des abstractions foireuses de Joel Spolsky, mais je reviendrai dessus. C’est à mon sens l’un des meilleurs articles écrit sur l’architecture logicielle.

Bienvenu à Todd McDermid!

La honte sur moi, j’avais oublié Todd McDermid dans le blogroll BI, à droite du site là –>

Pour rappel, c’est un poid lourd de SSIS sur la net, il est l’auteur de nombreux composants additionnels sur le CodePlex, dont le SCD Kimball, il est également MVP SQL Server.

Ne vous inquiétez pas, maintenant tout est réparé. Je l’ai ajouté à mes flux RSS, je surveille ses nouveaux articles et pour me rattraper je ferais bientôt une petite compilation de ses meilleurs articles.