dimanche 1 janvier 2012

[SSIS] Créer un fichier XML à partir d’une table SQL

A premier abord, cela parait anodin pour un ETL et pourtant nativement il n’existe pas de composant pour envoyer un flux de donnée vers un fichier XML. Confronté à cette problématique chez un client, j’ai dû creuser pour trouver des solutions de contournement à cette carence de SSIS.
Bien heureusement SSIS a l’avantage d’offrir la possibilité de recourir à .NET via des scripts La solution que j’ai trouvée, il y en a d’autre, notamment celle de François Jehl que vous pourrez trouver ici est d’utiliser l’objet DataSet ADO.NET.
L’utilisation de XML dans un DataSet est très bien documentée ici.
L’avantage de cette méthode est de pouvoir générer facilement du XML aussi bien introduire des types complexes, que de définir des colonnes comme attribut d’autres éléments.
Pour cette méthode, il suffit d'implémenter le code suivant dans un script task dans le control flow. Tout le travail (connexion à la base, lecture des tables, transformation XML et écriture dans le fichier) se feront grâce au script ci-dessous.
Voici le script :
//Ne pas oublier la reference.
using System.Data.SqlClient;


public void Main()
{
SqlConnection sqlConn;
// définition des 2 tables liées par un ID
string CurrencyQuery = "SELECT [CurrencyCode],[Name] FROM [Sales].[Currency]";
string CurrencyRateQuery = "SELECT [FromCurrencyCode],[ToCurrencyCode],[AverageRate],[EndOfDayRate] FROM [Sales].[CurrencyRate]";

try
{
    // Connexion à la base AdventureWorks (qui doit être une connexion ADO.NET)
    sqlConn = (SqlConnection)(Dts.Connections["AdventureWorks"]).AcquireConnection(Dts.Transaction);
    sqlConn = new SqlConnection(sqlConn.ConnectionString);
    sqlConn.Open();


    SqlCommand cmdCurrency = new SqlCommand(CurrencyQuery, sqlConn);
    SqlCommand cmdCurrencyRate = new SqlCommand(CurrencyRateQuery, sqlConn);
   
    //permet de remplir les dataset - c'est un lien entre la source et le dataset
    SqlDataAdapter CurrencyAdapter = new SqlDataAdapter(cmdCurrency);
    SqlDataAdapter CurrencyRateAdapter = new SqlDataAdapter(cmdCurrencyRate);

    // création de notre DataSet dont le nom sera la racine de notre fichier XML
    DataSet ds = new DataSet("CurrencyRate");

    //mise en mémoire des 2 tables
    CurrencyAdapter.Fill(ds, "Currency");
    CurrencyRateAdapter.Fill(ds, "CurrencyRate");

    // cette commande attribut est très intéressant il peut prendre plusieurs valeurs dont celle d'attribut.
    ds.Tables["Currency"].Columns["name"].ColumnMapping = MappingType.Attribute;

    // c'est ici que l'on va définir notre type complexe XML en établissant une relation entre les 2 datasets.
    DataRelation CurrencyRateDR = ds.Relations.Add("CurrencyRateRelation", ds.Tables["Currency"].Columns["CurrencyCode"], ds.Tables["CurrencyRate"].Columns["FromCurrencyCode"]);
    // propriété très importante puisqu'elle permet d'imbriquer les éléments enfants dans les éléments parents. Ils sont considéré par défaut comme éléments frère
    CurrencyRateDR.Nested = true;

    ds.WriteXml(new System.IO.StreamWriter("D:\\TEST\\test.xml"));
    sqlConn.Close();
}
catch
{
    Dts.TaskResult = (int)ScriptResults.Failure;
    throw;
}

Dts.TaskResult = (int)ScriptResults.Success;
}
Bien entendu, ici la source est une table XML, mais il est possible d’attaquer d’autres sources directement comme Excel par exemple. J’espère que cela pourra vous aider dans vos projets. A bientôt.

Aucun commentaire:

Enregistrer un commentaire