Exporter une table SQL Server dans un fichier XML avec SSIS

Exporter les données depuis une table SQL Server vers un fichier au format XML avec SSIS.

Pour exporter une table SQL Server au dans un fichier de type XML avec un package SSIS et sans composant additionnel, il faut transformer les données SQL en XML avec une requête. SSIS ne fournit pas de composant natif XML, il existe seulement la source XML. D’autres éditeurs de logiciels proposent des composants pour exporter en XML, comme le package SSIS productivity pack de KingswaySoft par exemple.

Tutoriel SSIS pour exporter des données SQL au format XML

Pour exporter les données de la table au fichier XML, voici les étapes. Créer la table d’exemple, construire la requête source avec les fonctions XML SQL Server natives, configurer le composant OLE DB source puis le composant Fat File Destination, et enfin exécuter le package.

1. Créer la table SQL source avec les données à exporter en XML

-- Créer la table des clients
CREATE TABLE [dbo].[Customers](
   [CustomerID] [int] NOT NULL,
   [FirstName] [nvarchar](20) NULL,
   [LastName] [nvarchar](20) NULL,
   [City] [nvarchar](20) NULL,
   [Country] [nvarchar](50) NULL,
   CONSTRAINT [CustomersPrimaryKeyCustomerID] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
);
GO

-- Insérer 8 lignes à exporter au format XML
INSERT INTO [dbo].[Customers] VALUES (1, N'Ali', N'Ahmed', N'Cairo', N'Egypt');
INSERT INTO [dbo].[Customers] VALUES (2, N'Johnny', N'John', N'Toronto', N'Canada');
INSERT INTO [dbo].[Customers] VALUES (3, N'John', N'Doe', N'Mexico City', N'Mexico');
INSERT INTO [dbo].[Customers] VALUES (4, N'Shu', N'Abbas', N'Paris', N'France');
INSERT INTO [dbo].[Customers] VALUES (5, N'Jeane', N'Raffin', N'Liushutun', N'China');
INSERT INTO [dbo].[Customers] VALUES (6, N'Legra', N'Leate', N'Błaszki', N'Poland');
INSERT INTO [dbo].[Customers] VALUES (7, N'Sullivan', N'Goadby', N'Xiaoguwei', N'China');
INSERT INTO [dbo].[Customers] VALUES (8, N'Tom', N'Ellams', N'Lop Buri', N'Thailand');

2. Construire la requête pour transformer les données SQL en XML

Après avoir créé la table et insérer les données des clients, construire la requête SQL Server pour transformer les données SQL en XML. Voici tout d’abord une requête SQL classique pour sélectionner les données.

-- Requête SQL classique pour sélectioner les données clients
SELECT
	[CustomerID],
	[FirstName],
	[LastName],
	[City],
	[Country]
FROM [dbo].[Customers]; 
Vérifier les données dans la table source avec SSMS
Vérifier les données dans la table source avec SSMS

Voici maintenant la requête avec les fonctions XML pour créer les éléments XML Customers et Customer.

-- Requête XML
SELECT 
	[CustomerID],
	[FirstName],
	[LastName],
	[City],
	[Country]	
FROM [dbo].[Customers]
FOR XML PATH('Customer'),
		ROOT('Customers'),
			ELEMENTS XSINIL;
Modifier la requête SQL pour transformer les lignes au format XML
Modifier la requête SQL pour transformer les lignes au format XML

Cliquer sur le lien dans le résultat pour afficher le fichier au format XML dans une nouvelle fenêtre SSMS.

Table des clients au format XML depuis SSMS
Table des clients au format XML depuis SSMS

Les données retournées par la requête XML sont de type IMAGE. C’est à dire que c’est un fichier. Pour transformer le type IMAGE SQL Server en texte, inclure la requête en tant que sous requête. La colonne est également renommée en XML_Column pour faciliter le mapping depuis le flux de données SSIS.

SELECT (
	SELECT 
		 [CustomerID],
		 [FirstName],
		 [LastName],
		 [City],
		 [Country]	
	FROM [dbo].[Customers]
	FOR XML PATH('Customer'),
			ROOT('Customers'),
				ELEMENTS XSINIL
) AS XML_Column;
Requête XML dans une sous requête pour afficher du texte
Requête XML dans une sous requête pour afficher du texte

3. Configurer le flux SSIS depuis la table SQL source vers le fichier XML

Ajouter les composant SSIS OLE DB Source et Flat File Destination dans le data flow.

Flux de données SSIS pour exporter une table SQL sans un fichier XML
Flux de données SSIS pour exporter une table SQL sans un fichier XML

Ouvrir le composant OLE DB Source et utiliser une connexion à la base de données source. Entrer la requête depuis l’étape précédente.

Insérer la requête source pour retourner la colonne au format XML
Insérer la requête source pour retourner la colonne au format XML

En cas de changement de requête, vérifier que le type de données est bien de type DT_NTEXT, soit au format Texte Unicode, pour éviter les erreurs au niveau du mapping vers le fichier. Faire un clic-droit et choisir Show Advanced Editor.

Vérifier le format de la colonne XML
Vérifier le format de la colonne XML

4. Configurer le fichier XML à exporter depuis SSIS

Depuis le composant Flat File Destination, Créer une nouvelle connexion de type fichier plat. Choisir le type Delimited.

Créer une nouvelle connexion de type fichier plat pour exporter le fichier XML
Créer une nouvelle connexion de type fichier plat pour exporter le fichier XML

Pour paramétrer le fichier, suivre ces étapes:

  1. Renommer le fichier XML : XML File Export
  2. Choisir le chemin sur le disque : C:\data\Customers_Data_Export.xml
  3. Sélectionner Unicode pour assurer le mapping avec la colonne source.
Configurer les options du fichier cible XML à exporter
Configurer les options du fichier cible XML à exporter

Par défaut, Integration Services utiliser le type DT_WSTR avec une longueur de 255 caractères. Paramétrer maintenant la colonne.

  1. Depuis l’onglet Advanced
  2. Renommer la colonne avec le même nom que la source : XML_Column
  3. Changer le type de données et utiliser le type Texte Unicode: Unicode text stream [DT_NTEXT]
Renommer la colonne et changer le type en Unicode text stream DT_NTEXT
Renommer la colonne et changer le type en Unicode text stream DT_NTEXT

Vérifier aussi le mapping de la colonne depuis l’onglet Mappings.

Mapping depuis la table source SQL vers le fichier XML à exporter
Mapping depuis la table source SQL vers le fichier XML à exporter

5. Exécuter le package pour exporter la table en XML et vérifier le contenu

Exécuter enfin le package pour exporter effectivement les données dans le fichier XML. Une seule ligne est transférée car la colonne contient l’intégralité du fichier au format XML et non des lignes classiques au format SQL.

Exécuter le package SSIS pour exporter la table dans le fichier XML
Exécuter le package SSIS pour exporter la table dans le fichier XML

Vérifier ensuite le fichier exporté, c’est à dire depuis C:\data\Customers_Data_Export.xml. Ouvrir le fichier avec Google Chrome ou un autre navigateur Web open source comme Mozilla Firefox par exemple. Et vérifier les données et la structure.

Ouvrir le fichier XML avec Google Chrome pour afficher la structure
Ouvrir le fichier XML avec Google Chrome pour afficher la structure

Conclusion sur l’export de fichier en XML avec SSIS

Pour conclure, exporter une table SQL Server vers un fichier au format XML n’est pas guidé complètement car SSIS ne propose pas de composant natif pour l’export XML. Cependant, avec du code T-SQL et les composants disponibles, il est possible d’adapter la requête à la structure des données et de configurer les composants avec le type de données adéquat.

Ressources utiles sur l’export de données avec SQL Server

Tutoriels SSIS pour débutants

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut