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]; 

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;

Cliquer sur le lien dans le résultat pour afficher le fichier au format XML dans une nouvelle fenêtre 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;

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.

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.

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.

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.

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.

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]

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

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.

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.

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

• Exporter des données SQL dans un fichier texte avec SSIS
• Exporter une table SQL dans un fichier Excel avec SSIS
• Export de données SQL Server vers Excel avec SSMS

Tutoriels SSIS pour débutants