Datafly.pro

Excel est bien souvent une source de données utile, en particulier pour importer certaines données renseignées directement par l'utilisateur avec son application bureautique : le fichier est déposé par l'utilisateur, mis à disposition de SSIS qui le chargera à la prochaine exécution du package.

Mais ce n'est pas sans quelques problèmes souvent rencontrés et liés à ce type de source de données :

Le 64 bits : la source Excel de SSIS ne fonctionne qu'en 32 bits !

Vous êtes en 64 bits ? Si l'interface de conception est bien en 32 bits et ne pose donc pas de problème, vous devrez utiliser le mode 32 bit pour le debug et pour l'exécution dès lors que vous importez du fichier Excel.

Pour le debug, vous devrez modifier dans les propriétés du projet le paramètre Run64bitsRuntime :

Pour l'exécution, il faudra lancer la version 32 bits de l'exécutable DTExec : vous le trouverez dans C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn  (en SQL 2012, sinon changer le chemin en fonction de la version). Si le package est lancée par une tâche planifiée de l'agent SQL Server, vous pouvez aussi cocher la case "Runtime 32 bits" dans la configuration de l'étape spécifique aux packages SSIS.

Le pilote manquant

Dans certains cas, vous serez amené à installer le pilote (message d'erreur Le fournisseur 'Microsoft.ACE.OLEDB.12.0' n'est pas inscrit sur l'ordinateur local). Le pilote à installer est le "Access Database Engine 2010 redistributable SP2". Vous le trouverez sur http://www.microsoft.com/fr-fr/download/details.aspx?id=13255

Il existe en version 32 et 64 bits. Il nous faut la version 32 bits (voir plus haut) et petit problème, celle-ci ne veut s'installer que si aucune application Office en 64 bits n'est installée sur l'ordinateur (de même, vous ne pouvez pas installer côte à côte le pilote x86 et x64 sur la même machine) ! Vous pouvez bien sûr désinstaller tout composant Office x64, mais il existe aussi une astuce que vous pouvez tenter (bien que non supportée) : lancer l'installation du pilote avec le paramètre /passive  (cela fonctionne, mais reste à vos risques et périls).

Les types de données pris en charge : Excel ne connait que quelques types de données

Seuls les types de données suivants existent avec une table Excel :

• Double-precision float (DT_R8)
• Currency (DT_CY)
• Boolean (DT_BOOL)
• Date and time (DT_DATE)
• Unicode string with a length of 255 (DT_WSTR)
• Unicode text stream (DT_NTEXT)

A vous d'utiliser les conversions de type dans SSIS pour obtenir les types de données cibles...!

La reconnaissance automatique des types de données effectuée par le pilote

Lorsque vous sélectionnez une table Excel à importer, le type de données des colonnes est déterminé automatiquement. Il se base pour chaque colonne sur le contenu des 8 premières lignes (par défaut).

C'est simple à tester avec l'aperçu de la source Excel dans SSIS. Si pour une colonne, ces 8 premières lignes contiennent en majorité des nombres, le pilote va déterminer automatiquement que cette colonne est de type numérique. Toute valeur caractère sera remplacée par un NULL !

Vous trouverez les mêmes problèmes de type avec des colonnes caractère où seulement certaines lignes ont des chaînes qui dépassent 255 caractères : le pilote peut donc choisir selon les cas DT_WSTR (255 caractères max) ou DT_NTEXT...

Dans l'exemple ci-dessous, colonne1, 3 valeurs seulement sont de type texte parmi les 8 premières lignes : SSIS type la colonne comme numérique !

Pour résoudre ce problème, différentes solutions :

• Typer la colonne en TEXTE directement sous Excel : de loin la meilleure solution !
• Ajouter l’option IMEX=1 à la chaîne de connexion : force les types mixtes à texte (mais reste toujours basé sur les 8 premières valeurs !)
• Modifier le registre, option TypeGuessRow à 1 pour forcer la détection du type sur une seule ligne plutôt que 8. Il faut bien sûr que la première valeur soit du texte, de plus c'est une option globale pour l'ordinateur...

Et bien sûr, les changements de structure intempestifs par l'utilisateur

Ils peuvent mettre en défaut l'importation pour peu qu'on modifie la structure, c'est bien le problème de laisser un fichier Excel à renseigner par l'utilisateur. Utiliser un formulaire de saisie peut brider l'utilisateur et rendre la structure plus stable, mais dans ce cas à quoi bon Excel ?

Moralité

Pour quelque chose de fiable, laissez tomber Excel... Faire saisir l'utilisateur dans une table de base de données (une petite interface de saisie est nécessaire), ou préférer des fichiers plats ou CSV figés et stables qui seront beaucoup plus efficaces à importer (et en 64 bits de surcroît...)