SQL Server 2005 Integration Services et XML

Fleur-Anne BLAIN

Ce tutoriel a pour objectif d'initier à l'utilisation de documents XML via SQL Server Integration Services (SSIS) à travers l'intégration de données dudit fichier dans une base de données SQL Server 2005. Nous allons aussi présenter quelques opérations applicables à des fichiers XML via une XML Task. ♪

- Titre : SQL Server 2005 Integration Services et XML

- Auteur : Fleur-Anne BLAIN

- Parution : 26 mars 2009

- Mise à jour : 26 mars 2009

- Licence : Copyright ® 2011 Fleur-Anne BLAIN. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- Introduction

- I. Présentation de notre exemple

- II. Import XML vers SQL Server 2005

- III. Petits plus XML Task : Merge et comparaison de deux fichiers XML

- III-A. Merge de deux fichiers XML

- III-B. Comparaison de deux fichiers XML

- Conclusion

- Liens utiles

- Remerciements

Introduction

Très souvent, les fichiers XML sont utilisés à des fins de stockage de données de par leur portabilité au sein d'un système d'information et de la facilité de leurs utilisations. SSIS permet d'intégrer facilement le contenu de fichiers XML dans une base de données. Notre objectif est d'intégrer les données de notre fichier XML dans notre base de données via SSIS. Pour cela, nous nous baserons sur un exemple simple afin d'en comprendre les principales étapes.

Dans ce tutoriel, nous aborderons la création de projets BI avec SQL Server 2005, la création et configuration des sources de données ainsi que la création et configuration des destinations de données. De même, nous aborderons des opérations applicables à des fichiers XML, en particulier en amont d'un import de ceux-ci.

Dans un premier temps, nous présenterons notre exemple à savoir le fichier XML que nous allons intégrer ainsi que la destination de nos données à savoir la table proprement dite. Puis dans un second temps, nous verrons l'injection des données issues du XML dans notre base de données via SSIS. Enfin, nous allons découvrir les opérations de Merge et Diff pour différence de fichiers XML via une XML Task.

I - Présentation de notre exemple

Prérequis : d'un point de vue logiciel, vous devez disposer de Microsoft Visual Studio 2005 et Microsoft SQL Server 2005 au minimum (en dehors des versions Express qui ne prennent pas en charge SSIS). D'un point de vue connaissance, si vous n'êtes pas familiarisés avec SSIS, je vous conseille de lire ce tutoriel :

Présentation de SQL Server Intgération Services : SSIS, car nous ne reviendrons pas sur toutes les bases de l'outil.

Avant d'importer des données, il faut bien que notre fichier XML en contienne. Ci-dessous, notre petit exemple de produits que nous allons importer :

Liste de produits

<produits>
<produit>
<libelle>Brosse à dents</libelle>
<nb>1</nb>
<prix>2</prix>
</produit>
<produit>
<libelle>lessive</libelle>
<nb>2</nb>
<prix>16</prix>
</produit>
<produit>
<libelle>Mouchoirs</libelle>
<nb>10</nb>
<prix>3</prix>
</produit>
<produit>
<libelle>bonbons</libelle>
<nb>2</nb>
<prix>5,99</prix>
</produit>
<produit>
<libelle>fruits</libelle>
<nb>5</nb>
<prix>1</prix>
</produit>
</produits>

Ce sont donc ces données ci-dessus que nous allons importer. De même, avant d'importer, il faut que notre destination de données existe. En l'occurrence, ci-dessous le script SQL permettant de créer notre table. Bien évidemment, il faut que notre base de données existe.

Table de produits

USE [ssis-xml]
GO
/****** Object:  Table [dbo].[Produits]    Script Date: 03/14/2009 18:21:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Produits](
[Nom] [nvarchar](50) NULL,
[quantite] [int] NULL,
[Tarif] [float] NULL
) ON [PRIMARY]

II - Import XML vers SQL Server 2005

Après avoir lancé Microsoft Visual Studio, nous créons un nouveau projet de type Integration Services. Cela nous donnera accès à toute la fameuse boite à outils SSIS. Nous allons développer cette partie par étape : création et configuration de la source de données (notre fichier XML) et création et configuration de la destination (notre table de base de données SQL Server 2005).

Dans un premier temps, il nous faut spécifier quel type de flux de contrôle nous allons utiliser. Nous choisissons tâche de flux de données comme suit :

Ensuite, nous passons à l'onglet flux de données. Pour notre import, la source de données est un fichier XML. Donc nous choisissons bien évidemment la source de données XML.

Passons à la configuration de notre source de données. Nous double cliquons sur notre composant source. Nous sélectionnons notre fichier XML, ici ssis-xml.xml. Puis, comme nous ne disposons pas de fichier XSD, SSIS est très gentil et nous propose d'en générer un pour nous. Ce que nous choisissons. Notre manager de connexion au fichier est donc configuré. Passons aux colonnes de notre XML que nous souhaitons importer. Pour cela, il suffit de cocher ou décocher les colonnes que nous souhaitons choisir ou ignorer lors de l'import.

Enfin, nous validons notre source de données. Voilà elle est utilisable.

Passons à notre destination de données à savoir notre table Produits au sein de SQL Server 2005. Nous choisissons une destination OLEDB Destination que nous allons paramétrer dès maintenant.

Nous relions notre source à notre destination avant de paramétrer celle-ci à l'aide de la flèche verte. Notre flux devient visible :

Remarque : notre destination affiche une petite croix rouge, signe d'une erreur. Normal, nous ne l'avons pas encore paramétrée. Cependant, dès que nous voyons ce type de croix, inutile d'exécuter le package il y aura une erreur. Par anticipation, il nous suffit de survoler cette croix afin de voir le message d'erreur.

Afin de configurer notre destination, nous double cliquons dessus. Nous débutons par le gestionnaire de connexion :

Nous sélectionnons la chaîne de connexion. Bien entendu, si celle-ci est inexistante, il faut la créer. Puis nous sélectionnons le type de destination à savoir table ou vue et enfin notre table : Produits. Passons au mapping. Nous associons nos données sources à nos attributs de table destination.

Nous validons nos opérations. Note flux est complet. Nous pouvons alors l'exécuter (F5).

Bien entendu, tout est vert…ça marche :D. Il suffit de le vérifier au sein de la base de données :

III - Petits plus XML Task : Merge et comparaison de deux fichiers XML

Dans cette partie, nous allons effectuer deux opérations. La première consiste à merger, regrouper deux XML en un seul fichier XML. Ceci peut-être utile en amont d'un import quelconque. De même, nous allons voir comment comparer deux fichiers XML afin d'en dégager les différences.

Le tronc commun à ces deux opérations est la création du XML task. Pour cela, au niveau de l'onglet Control Flow ou flux de contrôle, nous allons ajouter une tâche XML comme suit :

Remarque : L'erreur symbolisée par la petite croix rouge est normale. Nous n'avons pas encore paramétré notre composant XML Task.

III-A - Merge de deux fichiers XML

Nous disposons de deux fichiers XML que nous souhaitons regrouper en un seul. Nous allons dupliquer notre exemple XML et nous allons paramétrer notre tâche de telle sorte que le regroupement se fasse dans un 3e fichier résult.xml. Bien entendu, il est possible de faire ce regroupement dans l'un des deux fichiers sources au risque de perdre l'original.

Par défaut, notre XML Task ressemble à ceci :

C'est cet écran que nous allons paramétrer. Il nous faut définir :

- Le type d'opération : Merge

- Notre type du premier opérande : une connexion de fichier

- Notre premier opérande : ledit fichier ssis-xml.xml

- Le type du second opérande : une connexion de fichier, nous disposons d'un fichier XML

- Le second opérande : notre fichier XML ssis-xml2

- Notre type de destination : une connexion de fichier, nous allons écrire les résultats dans notre fichier result.xml

- Le type de destination : une connexion de fichier

Nous obtenons alors ceci :

Que nous validons. Puis nous exécutons notre opération de merge comme suit :

Notre opération a réussi :

Voyons ce que donne le résultat de notre fichier xml result.xml :

results.xml

<produits>
<produit>
<libelle>Brosse a dents</libelle>
<nb>1</nb>
<prix>2</prix>
</produit>
<produit>
<libelle>lessive</libelle>
<nb>2</nb>
<prix>16</prix>
</produit>
<produit>
<libelle>Mouchoirs</libelle>
<nb>10</nb>
<prix>3</prix>
</produit>
<produit>
<libelle>bonbons</libelle>
<nb>2</nb>
<prix>5.99</prix>
</produit>
<produit>
<libelle>fruits</libelle>
<nb>5</nb>
<prix>1</prix>
</produit>
<produit>
<libelle>Brosse a dents</libelle>
<nb>1</nb>
<prix>2</prix>
</produit>
<produit>
<libelle>lessive</libelle>
<nb>2</nb>
<prix>16</prix>
</produit>
<produit>
<libelle>Mouchoirs</libelle>
<nb>10</nb>
<prix>3</prix>
</produit>
<produit>
<libelle>bonbons</libelle>
<nb>2</nb>
<prix>5.99</prix>
</produit>
<produit>
<libelle>fruits</libelle>
<nb>5</nb>
<prix>1</prix>
</produit>
</produits>

III-B - Comparaison de deux fichiers XML

La comparaison de deux fichiers XML correspond à l'opération Diff dans une tâche XML. Notre second fichier XML permettant la comparaison sera l'initial de notre exemple légèrement modifié comme suit :

<produits>
<produit>
<libelle>Brosse a dents</libelle>
<nb>1</nb>
<prix>4</prix>
</produit>
<produit>
<libelle>lessive</libelle>
<nb>4</nb>
<prix>16</prix>
</produit>
<produit>
<libelle>Mouchoir</libelle>
<nb>10</nb>
<prix>3</prix>
</produit>
<produit>
<libelle>Chocolat</libelle>
<nb>4</nb>
<prix>5.99</prix>
</produit>
<produit>
<libelle>fruits</libelle>
<nb>5</nb>
<prix>1</prix>
</produit>
</produits>

Pour paramétrer cette comparaison de fichier, il nous faut définir :

- Le type d'opération : Diff

- Note type de premier opérande : connexion de fichier

- Notre premier opérande : ssis-xml.xml

- Note type de second opérande : connexion de fichier

- Notre second opérande : ssis-xml2.xml

- SaveDiffDiagram : true

- DiffDIagramType : connexion de fichier

- DiffDiagramDestination : result.xml

Nous obtenons alors via ce paramétrage ceci :

Nous validons et exécutons cette nouvelle tâche comme expliqué précédemment. Nous obtenons alors ceci dans notre fichier result.xml :

<?xml version="1.0" encoding="utf-16"?>
<xd:xmldiff version="1.0" srcDocHash="5237581266046920043" options="None" fragments="no" xmlns:xd="http://schemas.microsoft.com/xmltools/2002/xmldiff">
<xd:node match="1">
<xd:node match="1">
<xd:node match="3">
<xd:change match="1">4</xd:change>
</xd:node>
</xd:node>
<xd:node match="2">
<xd:node match="2">
<xd:change match="1">4</xd:change>
</xd:node>
</xd:node>
<xd:node match="3">
<xd:node match="1">
<xd:change match="1">Mouchoir</xd:change>
</xd:node>
</xd:node>
<xd:node match="4">
<xd:node match="1">
<xd:change match="1">Chocolat</xd:change>
</xd:node>
<xd:node match="2">
<xd:change match="1">4</xd:change>
</xd:node>
</xd:node>
</xd:node>
</xd:xmldiff>

Et si vous comparez minutieusement, c'est exactement les différences qui ressortent de cette opération.

Conclusion

Le fichier XML pouvant servir de moyen de stockage de données pratique, léger et facilement manipulable, il est intéressant de voir comment l'utiliser via SQL Server Integration Services. Nous avons effectué un import de fichier XML, mais aussi appliqué des opérations qui peuvent être utiles en amont d'un import. Je vous invite à inspecter minutieusement le comportement de SSIS, et surtout le rapport entre l'arborescence XML et la structure de notre table destination.

Liens utiles

- Présentation de SQL Server Integration Services : SSIS

- Automatisation de l'exécution d'un package SSIS sur Microsoft SQL Server 2005

- Forum Microsoft BI

- Tous les cours Microsoft BI

Remerciements

Je tiens à remercier Reskibil pour ses corrections.