SSIS et SharePoint

14/12/2015

SharePoint permet de structurer et stocker des informations. Ces informations vivent dans SharePoint et sont consultables par plusieurs biais (site, webservice, code). Nativement il est possible d’exporter ces données vers Excel. Vous pouvez aller encore plus loin dans l’export – et l’import- de données avec SSIS.

Qu’est-ce que SSIS ?

SSIS est l’ETL de Microsoft qui est une brique intégrée à SQL Server. ETL est l’acronyme de Extract Transform and Load. Comme son nom l’indique il s’agit d’un logiciel qui permet d’extraire les données d’une source A, de les travailler (filtrer, enrichir, combiner, convertir, …) puis de les exporter vers une destination B. La richesse d’un ETL vient de :

ses connecteurs, c’est à dire avec quoi peut-il communiquer en lecture/écriture (SQL Server, Oracle, fichier plat, SharePoint, …);
les opérations qu’il peut effectuer sur les données (filtre, agrégation, enrichissement, …).

Outils nécessaires

La démonstration consiste à récupérer les données d’une liste SharePoint, sélectionner les colonnes voulues, convertir les formats de données au besoin et exporter dans un fichier à plat.

Pour débuter dans le développement SSIS, vous aurez besoin de :

SQL Server 2012 avec SSIS;
SQL Server Data tools (SSTD), l’environnement de développement basé sur Visual Studio qui permet de créer des packages SSIS. Il est précédemment appelé BIDS (Business Intelligence Development Studio).
un environnement SharePoint;
un connecteur SharePoint pour SSIS.

Maintenant vous devez choisir votre connecteur. Vous en trouverez 3 gratuit en cherchant :

SharePoint List Source and Destination, Microsoft SQL Server Community Samples: Integration Services, sur codeplex
SSIS Connector for Sharepoint Online sur codeplex
Microsoft® OData Source for Microsoft SQL Server® 2012

<td width="192">
  <strong>SP Connector</strong>
</td>

<td width="192">
  <strong>SSIS Connector for SP Online</strong>
</td>

<td width="192">
  <strong>OData Source</strong>
</td>

<td width="192">
  X
</td>

<td width="192">
</td>

<td width="192">
  X
</td>

<td width="192">
  X
</td>

<td width="192">
</td>

<td width="192">
  X
</td>

<td width="192">
</td>

<td width="192">
  X
</td>

<td width="192">
  X
</td>

Le choix le plus judicieux est donc le composant OData Source de Microsoft.

Utilisation de OData Source pour SQL Server 2012

Une fois l’installation effectuée sur votre poste de développement, vous verrez apparaitre un nouvel élément dans VSDT.

Composant OData Source dans Data Tools

Dans le dataflow, vous glissez-déplacer un OData Source, un Data Conversion et un Flat File Destination pour obtenir le schéma suivant.

Dataflow

Vous devez configurer les différents composants. Dans le OData Source, vous devez renseigner l’URL de votre site SharePoint suffixé de /_vti_bin/listdata.svc. Précisez aussi les informations nécessaires pour l’authentification (Windows authentication ou un couple login/password).

Paramètre du gestionnaire de connexion OData

Si vous utilisez Office 365, vous devrez le préciser en passant Microsoft Online Services Authentication à True comme dans la capture ci-dessous :

Configuration de la connexion OData pour Office 365

Ensuite vous devrez préciser la liste d’où vous voulez récupérer des données. Il vous est possible de passer des paramètres de requêtes. Vous devez ensuite choisir les colonnes que vous voulez récupérer.

Choix de la source de données

Dernières étapes, convertir les données si nécessaire puis faire le mapping entre les colonnes sources et les colonnes du fichier à plat.

Ensuite vous exécutez le package SSIS et – si tout est bien configuré – vous verrez SSDT passer en mode DEBUG et présenter l’exécution en temps réel.

Exécution du Dataflow

Résolution des erreurs rencontrées

Error 1 : The package failed to load due to error 0xC0011008

Si votre package de contient pas d’erreur lors de la conception mais que message suivant apparait lors de l’exécution :

The package failed to load due to error 0xC0011008 « Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored. ». This occurs when CPackage::LoadFromXML fails.

Il faut configurer votre package pour ne plus s’exécuter en 64bits.

Solution Explorer> clic droit sur le projet, properties

Configuration properties > debugging > Run64BitRuntime : False

Résolution : The package failed to load due to error 0xC0011008

Error 2 : The remote server returned an error: (407) Proxy Authentication Required

Si quand vous testez votre connexion aux données, le message suivant apparait :

The remote server returned an error: (407) Proxy Authentication Required.

Alors vous devez configurer votre connexion dans Internet Explorer

Résolution : The remote server returned an error: (407) Proxy Authentication Required

Comme vous avez pu vous en rendre compte, il est relativement simple d’extraire des données de SharePoint avec SSIS. D’autant plus qu’aucune ligne de code n’est nécessaire dans notre exemple.

Références:

[SharePoint permet de structurer et stocker des informations. Ces informations vivent dans SharePoint et sont consultables par plusieurs biais (site, webservice, code). Nativement il est possible d’exporter ces données vers Excel. Vous pouvez aller encore plus loin dans l’export – et l’import- de données avec SSIS.