[SSIS] Créer un 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 C# ou VB.

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.

[SQL][Developpement] Supprimer de gros volume de données grâce au partitionnement

Supprimer de gros volumes de données peut être problématique si l’on se cantonne à utiliser l’instruction DELETE.

Cette dernière a le défaut de poser un verrou exclusif sur la table cible et comme toute transaction sur les données de journaliser les opérations dans le fichier transaction log afin de pouvoir rollback la transaction en cas de problème et d’assurer l’intégrité des données.

Si vous avez besoin de nettoyer une table de données sans supprimer la totalité de la table (sinon l’instruction TRUNCATE TABLE ferait l’affaire), il peut être judicieux d’utiliser les fonctionnalités du partitionnement.

L’idée directrice de cette méthode et d’affecter les données d’une table source partitionnée à une table cible partitionnée sur le même schéma de partitionnement. Dans la mesure où physiquement rien n’est ni modifié ni déplacé et que l’opération porte juste sur les métadonnées, le temps d’exécution de cette manipulation est quasiment instantanée.

Une fois le Switch de partition opéré vous pouvez très facilement lancer l’instruction TRUNCATE TABLE sur la table cible.

Petit rappel : les fonctionnalités de partitionnement sont disponibles à partir de la version 2005 de SQL SERVER.

Ci-dessous je vous ai préparé un petit script d’exemple pour vous illustrer cette manipulation. La manip est faite dans les étapes 7 et 8, avant il ne s’agit que de la préparation de la base.

-----------------------------------------------------------------------------------------

-- ETAPE 01: Création de la base de données

-----------------------------------------------------------------------------------------

Create Database DemoPartitionsDatabase

Go

-----------------------------------------------------------------------------------------

-- ETAPE 02: Création de la fonction de partition et scheme

-----------------------------------------------------------------------------------------

Use DemoPartitionsDatabase

Go

Create Partition Function MaFonctionDePartionnement (int)

As Range LEFT For Values (20110725,20110801)

Go

Create Partition Scheme MaFonctionScheme

As Partition MaFonctionDePartionnement ALL To ([PRIMARY])

Go

-----------------------------------------------------------------------------------------

-- ETAPE 03: Création de la table

-----------------------------------------------------------------------------------------

Create table FactTable (

[date] int not null,

col1 int null,

col2 int null,

col3 int null,

col4 int null,

col5 int null,

col6 int null,

col7 int null,

col8 int null,

col9 int null,

) ON MaFonctionScheme([date])

-----------------------------------------------------------------------------------------

-- ETAPE 04: Mettons un peu de volumétrie dans la table

-----------------------------------------------------------------------------------------

declare @startdate date='2011-07-25',

@i int=0

While @i<9

begin

;with

t0 as (select n = 1 union all select n = 1),

t1 as (select n = 1 from t0 as a, t0 as b),

t2 as (select n = 1 from t1 as a, t1 as b),

t3 as (select n = 1 from t2 as a, t2 as b),

t4 as (select n = 1 from t3 as a, t3 as b),

t5 as (select n = 1 from t4 as a, t4 as b),

result as (select top 1000000 row_number() over (order by n) as n from t5)

insert into FactTable

select (select convert(int,convert(varchar(10),dateadd(dd,@i,@startdate),112))),

n,n,n,n,n,n,n,n,n from result

set @i=@i+1

end

go

-----------------------------------------------------------------------------------------

-- ETAPE 05: Creation d'une vue pour suivre l'état du partitionnement

-----------------------------------------------------------------------------------------

create view [dbo].[partition_info] as

SELECT

OBJECT_NAME(p.object_id) AS ObjectName, i.name AS IndexName,p.index_id AS IndexID,ds.name AS PartitionScheme,p.partition_number AS PartitionNumber,fg.name AS FileGroupName,prv_left.value AS LowerBoundaryValue,prv_right.value AS UpperBoundaryValue,

CASE pf.boundary_value_on_right

WHEN 1 THEN 'RIGHT'

ELSE 'LEFT' END AS Range,

p.rows AS Rows

FROM sys.partitions AS p

JOIN sys.indexes AS i

ON i.object_id = p.object_id

AND i.index_id = p.index_id

JOIN sys.data_spaces AS ds

ON ds.data_space_id = i.data_space_id

JOIN sys.partition_schemes AS ps

ON ps.data_space_id = ds.data_space_id

JOIN sys.partition_functions AS pf

ON pf.function_id = ps.function_id

JOIN sys.destination_data_spaces AS dds2

ON dds2.partition_scheme_id = ps.data_space_id

AND dds2.destination_id = p.partition_number

JOIN sys.filegroups AS fg

ON fg.data_space_id = dds2.data_space_id

LEFT JOIN sys.partition_range_values AS prv_left

ON ps.function_id = prv_left.function_id

AND prv_left.boundary_id = p.partition_number - 1

LEFT JOIN sys.partition_range_values AS prv_right

ON ps.function_id = prv_right.function_id

AND prv_right.boundary_id = p.partition_number

WHERE OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0

select * from [dbo].[partition_info]

go

-----------------------------------------------------------------------------------------

-- ETAPE 06: Crééons une table de purge identique à la première

-----------------------------------------------------------------------------------------

Create table FactTablePurge (

[date] int not null,

col1 int null,

col2 int null,

col3 int null,

col4 int null,

col5 int null,

col6 int null,

col7 int null,

col8 int null,

col9 int null,

) ON MaFonctionScheme([date])

-----------------------------------------------------------------------------------------

-- ETAPE 07: Déplaçons (que dis-je affectons) les données à la table de purge

-----------------------------------------------------------------------------------------

alter table dbo.FactTable switch partition 2 to dbo.FactTablePurge partition 2

go

select * from dbo.partition_info

go

-----------------------------------------------------------------------------------------

-- ETAPE 08: Il ne reste plus qu'à truncate la table de purge et à regarder le résultat

-----------------------------------------------------------------------------------------

truncate table dbo.FactTablePurge

go

select * from dbo.partition_info

go

/*****************************************************************************************

** The End : Les données ont disparu instantannément sans trace dans le fichier de log

*****************************************************************************************/

/*****

Si vous n'êtes pas convaincu par cette méthode essayez d'éxécuter le delete sur les 7 millions de lignes avec la requête ci-dessous

--delete from FactTable where $partition.MaFonctionDePartionnement(date)=2

*******/