Blog Homsys

La tâche SSIS (Microsoft SQL Server Integration Services) de chargement en bloc est utile pour charger un fichier plat dans une table d’une base de données dans les 2 cas suivants:

• Le chargement d’un fichier volumineux
• Le chargement d’un fichier à champs fixes contenant un nombre de colonnes important (ce qui permet un gain de temps non négligeable en phase de développement)

Tâche d’insertion en bloc

Son utilisation n’étant pas toujours triviale, voici une description des différentes étapes à suivre :
• 1) Création d’une connexion vers le fichier texte à charger
• 2) Utilisation de la tâche d’insertion en bloc (présente dans le flux de contrôle)
• 3) Paramétrage de l’onglet Connexion de la tâche d’insertion en bloc
• 4) Paramétrage de l’onglet Options de la tâche d’insertion en bloc

Etape 3) Paramétrage de l’onglet Connexion de la tâche d’insertion en bloc:

Tâche d’insertion en bloc – Onglet Connexion

• Connexion : Choisir la connexion vers la base de données dans laquelle les données seront chargées.
• DestinationTable : Choisir le nom de la table dans laquelle les données seront insérées. La table devra contenir l’ensemble des colonnes présentes dans le fichier.
• File : Connexion vers le fichier plat source.
• Format : Spécifier la valeur Utiliser un fichier pour charger un fichier dont le format est décrit dans un fichier de format .fmt. Pour les fichiers au format délimité sélectionner le format Spécifier.

Options pour les fichiers au format champs fixes :

• FormatFile : Définir le chemin d’accès vers le fichier .fmt décrivant le format du fichier.

Options pour les fichiers au format délimité :

• RowDelimiter : Définir les caractères séparateurs de lignes (en général {CR}{LF} en environnement windows).
• ColumnDelimiter  : Définir les caractères séparateurs de colonnes

Le chemin utilisé vers le fichier de format dans la propriété FormatFile doit être au format UNC (Universal Naming Convention): \\NomServeur\Partage.

Etape 4) Paramétrage de l’onglet Option de la tâche d’insertion en bloc

Tâche d’insertion en bloc – Onglet Options

• Options : Décocher les différents contrôles.
• SortedDate : Laisser la valeur non renseignée par défaut.
• MaxErrors : Laisser la valeur 0
• CadePage : Spécifier la valeur RAW
• DataFileType : Spécifier la valeur char
• BatchSize : Laisser la valeur 0
• LastRow : Laisser la valeur 0
• FirstRow : Laisser la valeur 1 (sauf si le fichier contient un en-tête qui ne doit pas être chargé)

Exemple de fichier .fmt:

Le fichier .fmt permet de décrire le format du fichier à charger. Il liste les colonnes contenues dans le fichier ainsi que leurs caractéristiques. Sa construction est en général rapide à partir d’un document excel décrivant les colonnes du fichier (pour ceux qui le souhaitent, j’ai un exemple de fichier permettant la génération automatique de fichier fmt à partir de formules excel).

Voici un exemple de fichier .fmt  simple :

10.0

2

1 SQLCHAR 0 3 «  »       1 CH1 French_CI_AS

2 SQLCHAR 0 5 « \r\n » 2 CH2 French_CI_AS

• La première ligne correspond à la version SQL Server utilisée (10.0 pour la version 2008, 9.0 pour la version 2005, 8.0 pour la version 2000 et 7.0 pour la version SQL 7…)
• La deuxième ligne précise le nombre de colonnes présentes dans le fichier
• Les lignes suivantes décrivent chaque colonne du fichier en précisant : la position de la colonne, SQLCHAR, 0, le nombre de caractères de la colonne, le numéro de la colonne, le nom de la colonne, la collation (FRENCH_CI_CI_AS).

La dernière ligne du fichier doit être un retour à la ligne.

La génération du fichier fmt peut être également effectuée automatiquement à partir de la table cible en utilisant la commande bcp.

Générer un fichier .fmt à partir de la commande bcp:

Ouvrir une fenêtre d’Invite de commande et exécuter la commande suivante :

bcp NomBaseDeDonnées.Schéma.NomTable format nul -c -f

CHGT_BLOC.fmt -T -S NomServeur\NomInstance

Exemple d’utilisation de la commande BCP

Conclusion:

Ce composant a l’avantage d’être performant. Il a par contre l’inconvénient d’être difficile à auditer en cas d’erreur. En effet, si le fichier source ne respecte pas strictement le format décrit, le fichier sera rejeté en bloc sans possibilité d’identifier la ligne ou la colonne ayant généré une erreur. Il n’est pas possible également de placer une visionneuse sur les données pour suivre le chargement.

Ce composant n’est donc à utiliser que sur des fichiers dont la structure est connue et bien maîtrisée. Pour des fichiers non volumineux et contenant un nombre de colonnes raisonnables, on préférera utiliser le chargement classique via des sources OLE DB.