Détecter le type de données Excel avec SSIS avant import

Détecter le type de données des colonnes Excel avec SSIS et éviter les erreurs d’intégration.

L’intégration des fichiers Excel via SSIS vous oblige à figer le type de données dans le composant OLEDB. En effet, le type de données Excel est déterminé dans SSIS par un échantillonnage des 8 premières lignes. Et ce depuis la colonne par défaut dans le composant OLEDB pour les sources Excel. Donc, le but est d’intégrer un fichier avec le format qui correspond à la source. Il vous faudra faire en sorte que les premières lignes du fichier soient dans un type compatible.

Table des matières

SSIS détecte le type des colonnes Excel par échantillonnage

Notez que par défaut, le nombre de lignes échantillonnées est de 8. Il est toutefois possible de modifier le nombre de lignes échantillonnées par le driver Excel. En effet, pour changer le nombre de lignes échantillonnées par le driver Excel Access Database Engine 12.0, changer la clef de registre comme ci-dessous:

Ouvrir l’éditeur de registre, pour ce faire, aller dans le menu démarrer, taper regedit.
Rendez vous dans sur une de ces clefs de registres, elle dépend de votre installation et du driver utiliser pour lire les fichiers Excel:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel

Changer ensuite la valeur de la clef appelée TypeGuessRows. Vous pouvez entrer des valeurs pour TypeGuessRows uniquement comprises entre 0 à 16 en décimal.

*Détecter le type de données Excel avec SSIS*

Erreurs de chargements de fichiers Excel avec SSIS

Premièrement, l’erreur la plus récurrente est de tenter d’intégrer un fichier Excel ayant des colonnes avec des chaînes supérieures à 255 caractères. Par exemple, les lignes échantillonnées ne contiennent aucune chaîne avec une longueur supérieure à 255 caractères. Alors le type de données détecté par SSIS dans le fichier Excel qui sera assigné à la colonne est : DT_WSTR(255) et non pas N_TEXT. De plus, la totalité des lignes avec une longueur supérieure à 255 caractères seront tronquées.

Erreur d’intégration d’une colonne Excel mixte avec texte et chiffres

Une autre erreur classique lorsque vous intégrez des fichiers Excel est la détection du type DT_NUMERIC au lieu du type DT_WSTR. Par exemple, si la colonne Excel contient des centaines ou des milliers de lignes. Avec des chiffres et des caractères, mais que les 16 premières lignes ne contiennent que des valeurs numériques. Le système SSIS l’interprète alors comme une colonne numérique.

C’est tout pour ce tutoriel technique sur la façon de détecter le type de données Excel avec SSIS en modifiant l’échantillonange via l’éditeur de registre de Windows.