Import de fichiers Excel avec SSIS

Ce type de source est loin d’être mon préféré et cela en raison du typage implicite des données effectué par le provider :

  • par défaut les colonnes texte sont typées en nvarchar(255) ou mémo (pour les chaines de plus de 255 caractères) et les numériques en réel. Pour convertir vos données dans le format désiré, vous n’avez plus qu’à utiliser un Data Conversion, je vous déconseille vivement de la faire via l’éditeur avancée mais bon (certains sont plus joueur que d’autres…)
  • de plus, quel que soit le provider (JET4.0 ou ACE12.0), le typage des données est effectué à l’aide d’un échantillonnage, par défaut sur les 8 premières lignes. Ce paramètre peut être modifié dans la base de registre

pour le Jet4.0 : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

pour le ACE 12.0 : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

  • si vous avez une colonne contenant des valeurs textuelles et numériques il vous faudra alors aussi gérer les types mixtes. Pour cela, vous n’avez qu’à modifier la chaîne de connexion en ajoutant aux propriétés étendues la valeur IMEX=1, exemple :

pour le Jet4.0 : Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\MyExcel.xls;Extended Properties= »Excel 8.0;HDR=Yes;IMEX=1« ;

pour le ACE 12.0 : Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\MyExcel.xlsx;Extended Properties= »Excel 12.0 xml;HDR=Yes;IMEX=1« 

Sachez toutefois que :

  • même si la valeur du TypeGuessRows est à 0, seules les 16 384 premières lignes sont scannées. Dommage si vous avez des chaines  longues de plus de 255 caractères ou bien des valeurs de types mixtes sur les lignes suivantes (cela m’est encore arrivé il y a peu), vous n’avez plus qu’à pleurer…
  • si le fichier Excel est en cours édition, il est verrouillé ce qui fera planté votre package SSIS
  • que le connecteur 64 bits pour Excel fonctionne, il suffit d’installer le ACE.OLEDEB.12.0 version 64 bits sur le serveur SSIS. Notez toutefois qu’il n’est pas possible d’installer les 2 versions du provider (32 et 64 bits) sur la même machine (mais qui de nos jours continue à installer Office sur les serveurs MS BI ?)

Mis à part ces considérations, les fichiers Excel peuvent s’avérer utiles du fait qu’un même classeur Excel puisse contenir plusieurs onglets (très pratique pour des tables de paramétrage « end-user »), et qu’un même onglet puisse contenir plusieurs tables. Rentrons dans le vif du sujet et voyons comment intégrer différentes tables contenues dans un seul et même onglet.

Lire la suite