Importer un fichier XML dans une table avec SSIS

Importer un fichier au format XML dans une table SQL Server avec un package SSIS.

Pour importer un fichier au format XML, il est nécessaire de fournir le schéma au format XSD qui représente la structure du fichier. Dans ce cas précis, le fichier XSD est généré automatiquement par Visual Studio et Integration Services. Ce tutoriel explique pas à pas comment importer un fichier XML dans une table SQL avec le composant natif XML Source.

1. Préparer la table des clients avec SSMS et le fichier XML à importer

La table de destination est la table des clients avec une structure basique. Voici le code SQL Server pour la créer depuis une connexion à la base cible avec SSMS.

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

Télécharger le fichier XML utilisé dans ce tutoriel depuis le lien ci-dessous. En raison de restrictions, le fichier est renommé en txt et avec des commentaires. Renommer donc le fichier en Customers_Data.csv et enlever les commentaires, c’est à dire supprimer la première et la dernière ligne du fichier.

En cas de difficulté pour télécharger et utiliser le fichier, voici un échantillon du fichier au format XML avec uniquement les 6 premières lignes.

<?xml version="1.0" encoding="UTF-8"?>
<Customers>
	<Customer>
		<CustomerID>1</CustomerID>
		<FirstName>Ali</FirstName>
		<LastName>Ahmed</LastName>
		<City>Cairo</City>
		<Country>Egypt</Country>
	</Customer>
	<Customer>
		<CustomerID>2</CustomerID>
		<FirstName>Johnny</FirstName>
		<LastName>John</LastName>
		<City>Toronto</City>
		<Country>Canada</Country>
	</Customer>
	<Customer>
		<CustomerID>3</CustomerID>
		<FirstName>John</FirstName>
		<LastName>Doe</LastName>
		<City>Mexico City</City>
		<Country>Mexico</Country>
	</Customer>
	<Customer>
		<CustomerID>4</CustomerID>
		<FirstName>Shu</FirstName>
		<LastName>Abbas</LastName>
		<City>Paris</City>
		<Country>France</Country>
	</Customer>
	<Customer>
		<CustomerID>5</CustomerID>
		<FirstName>Jeane</FirstName>
		<LastName>Raffin</LastName>
		<City>Liushutun</City>
		<Country>China</Country>
	</Customer>
	<Customer>
		<CustomerID>6</CustomerID>
		<FirstName>Legra</FirstName>
		<LastName>Leate</LastName>
		<City>Błaszki</City>
		<Country>Poland</Country>
	</Customer>
</Customers>

2. Configurer la source XML depuis le flux de données SSIS

Créer au préalable un package SSIS dans un projet, et y ajouter un flux de données. Ajouter ensuite les composants XML Source et OLE DB Destination dans le flux de données SSIS.

Ouvrir le composant XML et pointer vers le fichier XML.

1. Sélectionner le chemin du fichier XML à importer : C:\data\Customers_Data.xml
2. Utiliser Generate XSD et choisir le chemin du fichier: C:\data\Customers_Data_VisualStudio.xsd
3. Cliquer sur Columns pour vérifier le mapping

Le schéma XSD n’indique pas la longueur des champs par défaut et le package assigne donc le type DT_WSTR avec une longueur de 255 caractères. Soit le type de données SSIS (DT_WSTR,255). Le package affiche donc ce message d’avertissement:

Warning at {EF1A3256-9D27-4D85-9F3A-570513FF215F} [XML Source [69]]: No maximum length was specified for the XML Source.Outputs[Customer].Columns[FirstName] with external data type System.String. The SSIS Data Flow Task data type « DT_WSTR » with a length of 255 will be used.

3. Erreur SSIS No maximum length was specified for the XML Source

Ce n’est qu’un message d’avertissement mais il est possible de corriger le problème. Pour cela, modifier le fichier XSD et indiquer les longueurs pour tous les champs textes. Le fichier XSD généré automatiquement par Visual Studio est de ce type:

La ligne d’élément XML ne contient pas la propriété maxLength. Remplacer chaque ligne de ce type:

<xs:element minOccurs="0" name="FirstName" type="xs:string" />

Par un bloc avec la longueur explicite du champs texte, par exemple:

<xs:element minOccurs="0" name="FirstName" >
 <xs:simpleType>
   <xs:restriction base="xs:string">
	  <xs:minLength value="0"/>
	  <xs:maxLength value="20"/>
   </xs:restriction>
 </xs:simpleType>
</xs:element>

Le résultat est un fichier avec la longueur correspondante à chaque colonne de la table SQL. Ainsi le fichier modifié indique la longueur de chaque champs texte XML, comme sur la capture ci-dessous.

Enregistrer le nouveau fichier XSD, comme sous C:\data\Customers_Data.xsd. Pointer enfin le composant XML SSIS vers le nouveau schéma XML.

Une fois l’avertissement corrigé, vérifier le mapping des colonnes dans le composant source.

4. Configurer la connexion à la table de destination

Ouvrir le composant OLE DB Destination et choisir la table dans laquelle importer le fichier XML. C’est donc la table dbo.Customers créée à l’étape 1 ce de tutoriel.

5. Exécuter le package pour importer les données XML

Enfin, exécuter le package et vérifier que les données sont bien insérées sans erreur dans la table avec une requête SQL depuis SSMS.

Requête SQL pour vérifier les données importées.

SELECT * FROM [dbo].[Customers];

6. Conclusion sur l’import de données au format XML avec SSIS

Le composant XML Source SSIS est ergonomique, notamment avec l’option de génération automatique du schéma XSD directement depuis le fichier XML fourni. Toutefois, pour travailler en contrôle et éviter les warnings et les troncations, faire des ajustements manuels, notamment sur la longueur des champs textes.

Autres tutoriels sur l’intégration de données avec SSIS

• Importer un fichier CSV avec SSIS
• Importer un fichier Excel avec SSIS
• Comment importer plusieurs fichiers textes avec SSIS

Importer un fichier texte dans une base SQL avec SSIS