SSIS

Retour sur le SQLSaturday Rheinland

imageSamedi dernier avait lieu le SQLSaturday Rheinland en Allemagne, plus précisément à Sankt-Augustin, entre Cologne et Bonn.

La communauté française y était bien représentée puisque nous sommes 4 speakers (2 binômes) à y être allé : Florian Eiden, David Joubert et Jordan Mootoosamy.

Je félicite les organisateurs Oliver Engels, Tillmann Eitelberg et Kostja Klein, nos homologues allemands, pour cette superbe conférence.

Les points clés :

  • Beaucoup de speakers internationaux (dont des français)
  • Beaucoup de sponsors (20 !)
  • Beaucoup de cadeaux (cf. sponsors)
  • Un hackathon Big Data organisé la veille
  • Un lieu adapté (une université)
  • Un dîner speakers/sponsors vraiment agréable
  • Une visite touristique le dimanche (nous avions le train tôt et nous n’avons pas pu y participer)

image
©Dirk Hondong

C’est à chaque fois un réel plaisir de revoir la #SQLFamily (européenne) et de tisser ou consolider les liens avec nos homologues. On espère qu’ils seront aussi enchantés que nous lors de notre SQLSaturday à Paris en septembre.

image
©Dirk Hondong

 

Power Query v. SSIS

David et moi présentions une session plutôt technique (90% de démos) sur la bataille des ETLs.

Représentant Power Query dans le coin vert, j’ai sorti mes plus belles démos pour pousser les limites du Self-Service ETL.
Représentant le poids lourd SSIS dans le coin rouge, David a montré les capacités des packages.

Slide sur Slideshare : http://fr.slideshare.net/djeepy1/sqlsaturday-rheinland-2014-power-query-vs-ssis

Code des démos : OneDrive de Djeepy1

image

Certes, on a fait une fin à l’école des fans mais comme nous concluons, ”it’s a matter of Philosophy”

image

 

 

Agile Datawarehousing

Florian et Jordan ont présenté quant à eux une superbe session sur la BI Agile. L’agilité étant la marotte de Florian, je ne doutais pas une seule seconde de l’intérêt de la session et je n’ai pas été déçu. L’approche est excellente et je partage totalement tous les principes évoqués !!

Création de sorties asynchrones sur le composant script de SSIS

Cet article est un article invité du blog de Jean-Pierre.

Cet article va expliquer de manière détaillée comment générer un flux asynchrone à partir du composant Script de SSIS. C’est-à-dire générer une sortie déconnectée du flux d’entré.

Nous allons prendre un exemple trivial. En entré, nous avons un flux composé de 2 colonnes : NS et Reste. Ce flux provient d’un fichier avec un séparateur de colonne point-virgule. La colonne Reste contient 2 valeurs séparées par des virgules. Nous voulons obtenir 1 ligne en sortie pour chaque valeur de Reste et en conservant le même NS.

Fichier en entré :

NS;Reste
A;Hello,World
B;Salut,Monde

En sortie, nous souhaitons avoir 2 lignes par ligne d’entrée :

A Hello
A World
B Salut
B Monde

Il s’agit de « dépivoter » les données en entrée. Ce qui correspond à un UNPIVOT en SQL.

Mise en place de l’asynchronisme

Une fois le composant script branché sur une entrée, allez dans l’onglet Inputs and Outputs et passer la sortie en mode asynchrone (Propriété SynchronousInputID = None).

async_ssis1

Figure 1: Propriétés Inputs and Outputs du script

A la sortie MaSortie, ajoutez les deux colonnes du flux souhaitées en sortie. Pour des raisons de simplification, elles seront de type DT_STR et de taille 50. Cette étape se fait à la main et s’il y a beaucoup de colonnes, peut être fastidieuse.

async_ssis2

Figure 2: Ajout des colonnes de sortie

Il faut indiquer maintenant au script de traiter les lignes une par une. Cela est effectué en ajoutant dans le code, la méthode suivante :

public override void Input0_ProcessInput(Input0Buffer Buffer)
    {        
        //Tant qu'il y a des lignes à traiter
        while (Buffer.NextRow())
        {
            //Appel du traitement d'une ligne
            Input0_ProcessInputRow(Buffer);
        }

        //quand il n'y a plus de lignes
        if (Buffer.EndOfRowset())
        {
            //Fermeture du flux de sortie
            MaSortieBuffer.SetEndOfRowset();
        }
    }

Le nom de méthode est suffixé par le nom du flux d’entrée du composant. Dans notre exemple Input0. Pensez à le changer si besoin. Par défaut, le VSTA de SSIS 2008 ne donne pas le prototype de cette méthode, il faut l’écrire à la main.

Ce code renvoie chaque ligne (Row) lue du tampon (Buffer) d’entré vers une méthode de traitement.

Une fois le tampon d’entré entièrement lu, le tampon de sortie est fermé.

Le composant est prêt à être utilisé en mode asynchrone. Il ne reste qu’à écrire le traitement métier.

Traitement métier

Le code est très simple. Il s’agit de la méthode classique XXX_ProcessInputRow dont le prototype est présent dans le code (avec XXX le nom du flux d’entré). Dans notre exemple : Input0_ProcessInputRow.

Il suffit de maintenant de séparer les valeurs de la colonne Reste et en faire deux lignes de sortie (en utilisant le tampon MaSortieBuffer).

Le code de notre exemple est le suivant.

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        //Traitement d'une ligne

        //Séparation de la valeur de la colonne Reste par la virgule
        var valeurs = Row.Reste.Split(',');

        //Pour chaque valeur, il faut créer une ligne dans le flux de sortie et l'associer au NS
        foreach (var valeur in valeurs)
        {
            MaSortieBuffer.AddRow();
            MaSortieBuffer.NS = Row.NS;
            MaSortieBuffer.MaValeur = valeur;
        }
    }

Notons que c’est à ce niveau que la méthode AddRow est employée sur le tampon de sortie pour créer une ligne par valeur séparée.

Pour des raisons de simplification, aucun contrôle (taille, …) n’est effectué.

Résultat

En exécutant notre Data Flow, nous obtenons la séparation désirée, chaque valeur séparée par une virgule donne lieu à une ligne de sortie. Dans notre exemple, cela donne pour une ligne d’entré, deux lignes en sortie.

async_ssis3

Figure 3: Résultat de l’exécution de notre script

Il ne reste qu’à poursuivre le traitement SSIS avec nos données « dépivotées ».

Le code complet du script se trouve ci-dessous en annexe.

A quoi ça sert ?

Bonne question !

Dans une mission, il m’est arrivé de recevoir une colonne contenant une règle mathématique sous forme d’une liste de code A + B +C = D (bon, les règles étaient un peu plus complexes, il y avait des moins). La demande client était d’effectuer des agrégations de valeurs suivant ces règles. La valeur de code D est obtenue par la somme des valeurs des codes A, B et C. Un développeur .NET s’est chargé de développer un parseur syntaxique, me permettant d’obtenir un tableau des codes à sommer.

L’utilisation du code ci-dessus a simplement permis de normaliser la règle en deux colonnes code_parent et code_a_sommer. Et ensuite … c’est du SQL à base de SUM et GROUP BY.

Annexe : code complet du script

/* Microsoft SQL Server Integration Services Script Component
* Write scripts using Microsoft Visual C# 2008.
* ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

public override void PreExecute()
{
base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
}

public override void PostExecute()
{
base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
}

/// <summary>
/// Code de traitement asynchrone de l'entrée
/// Code à ajouter
/// </summary>
/// <param name="Buffer"></param>
public override void Input0_ProcessInput(Input0Buffer Buffer)
{
//Tant qu'il y a des lignes à traiter
while (Buffer.NextRow())
{
//Appel du traitement d'une ligne
Input0_ProcessInputRow(Buffer);
}

//quand il n'y a plus de lignes
if (Buffer.EndOfRowset())
{
//Fermeture du flux de sortie
MaSortieBuffer.SetEndOfRowset();
}
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
//Traitement d'une ligne

//Séparation de la valeur de la colonne Reste par la virgule
var valeurs = Row.Reste.Split(',');

//Pour chaque valeur, il faut créer une ligne dans le flux de sortie et l'associer au NS
foreach (var valeur in valeurs)
{
MaSortieBuffer.AddRow();
MaSortieBuffer.NS = Row.NS;
MaSortieBuffer.MaValeur = valeur;
}
}

public override void CreateNewOutputRows()
{
/*
Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
*/
}

}

Merci à JP pour mettre à disposition cet article en le publiant sur son blog.

SSIS On-demand – publier un package en OData Feed

 

Lors des dernières Journées SQL Server, j’ai présenté une session sur la gestion des données avec Power BI.

La majeure partie de la session était consacrée à Power Query et j’ai fait une démonstration de la consommation de données en provenance d’un package SSIS, tout cela dans Excel. Explication de texte…

 

Préambule

La solution technique repose sur un composant SSIS & un composant SQL Server fournis par Microsoft qui sont passés totalement inaperçus (sauf si vous suivez le blog de Matt Masson, la référence internationale sur SSIS).

Ce composant est :

SSIS Data Feed Publishing Components

Vous pouvez le télécharger ici : Télécharger SSIS Data Feed Components

Vous avez également un tutorial sur le site de Microsoft (mais je vous recommande le mien Tire la langue) : Publish SSIS packages as OData Feeds

J’en avais également parlé brièvement lors de ma session Power BI du dernier SQLSaturday Paris.

 

Etape 1 : SSIS

Autant vous le dire tout de suite, il va falloir redévelopper vos packages. Pas de panique, c’est juste la destination de vos packages qu’il va falloir modifier.
(En même temps, si vous y repensez, c’est tout à fait logique car il faut bien rediriger le flux de données.)

imageAprès l’installation de SSIS Data Feed Components, vous avez un nouveau composant destination de Data Flow : Data Streaming Destination

Il suffit juste d’envoyer les données que vous souhaitez rendre disponibles sous forme de Feed dedans.

image

Ici, je fais un bête transcodage entre une table et un fichier CSV, rien de bien passionnant mlais suffisant pour la démo.

 

Etape 2 : publication

Microsoft focalise sur la publication sous la forme de Data Feed (ou OData Feed). En réalité, techniquement, le composant SSIS Data Feed n’a rien à voir avec un feed.
Il publie tout simplement un package SSIS (je devrais dire un Data Flow) sous la forme d’une vue SQL.

Le composant que vous avez installé propose également un assistant que vous trouverez dans votre menu écran Démarrer sous le nom de Data Feed Publishing Wizard.

image

Ce wizard vous demande en premier lieu le package à publier. Il faudra donc au préalable déployer votre package (votre .ispac) dans un catalogue SSIS.

image

Ensuite, l’assistant vous propose de saisir une configuration pour votre package.

image

Ensuite, il vous demande où vous souhaitez publier le flux. On voit bien ici que la publication est sous la forme d’une Vue dans une base SQL Server.

image

Quelques paramètres comme la version du Runtime SSIS (32 ou 64 bits) ou le timeout mais aussi, l’assistant parle de Linked Server (??!?).

On voit d’ailleurs dans l’étape de validation qu’il installe et configure un serveur lié dans votre SQL Server

image

C’est là toute la magie de cette technologie. C’est en fait un driver OLEDB, utilisable comme un banal serveur lié, qui lance notre package et récupère les données streamées par le composant Data Streaming Destination.

En faisant un tour dans Management Studio, on voit bien les éléments :

image

Pour requêter mes données, je fais juste un simple SELECT :

SELECT * FROM dbo.MaVueSSIS

Cela permet un usage purement SQL Server et pas forcément avec un Feed OData.

 

Etape 3 : transformer en OData Feed

La troisième étape, c’est de transformer notre vue SQL en OData Feed. Pour cela nous allons avoir besoin de Power BI car il contient un module permettant d’exposer des données “simplement” à des utilisateurs. Ce module, c’est la Data Management Gateway mais pour que l’expérience soit complète, nous allons avoir besoin de Power Query et d’un site Power BI.

En raccourci, ça donne cela :

image

Je vous la fait courte sur la création de la Gateway entre votre tenant Power BI et votre SI (votre SQL Server si vous préférez).

  1. Téléchargement et Installation de la Data Management Gateway
  2. Configuration et ouverture de flux (port 8051 par défaut)
  3. Création d’une Gateway sur l’IT Admin Portal de votre tenant Power BI
  4. Configuration de la clé pour que votre DMG et Power BI communique

image

Pour publier notre package sous la forme d’un OData Feed, il suffit simplement de configurer une nouvelle source de données sur votre Gateway dans l’IT Admin Portal de votre site Power BI et ensuite d’exposer la vue SQL.

image

Dans les paramètres de la Data Source, on n’oubliera pas d’activer l’option OData pour que la source soit accessible de Power Query

image

 

Etape 4 : consommation depuis Power Query

La dernière étape, c’est de consommer notre package depuis Excel. Pour cela, je me rends dans Power Query, je lance une recherche et je n’ai qu’à “utiliser” la bonne query

image

On remarque bien que la source de données est vue par Power Query comme un flux OData et non comme une vue ou une table SQL Server.

OData, ça veut dire que le données passent par le Cloud ?

On est en droit de se poser la question mais qu’on se le dise de suite, les données ne transitent pas par le Cloud.

Pourtant, l’URL du feed OData est bien sur Azure ?!?

Voici ce qui se passe :

  1. La recherche interroge le Data Catalog qui contient les méta-données des Queries partagées et des Sources publiées, notamment l’URL
  2. Excel (Power Query) contacte le Cloud à l’URL du Feed OData
  3. Le Cloud lui donne les infos de connexion à la Gateway qui référence la source
  4. Excel communique directement avec la Data Management Gateway (et donc ne passe pas par le Cloud)

On peut vérifier cela avec un outil comme Fiddler qui trace les échanges HTTP.

image
(Cliquer pour agrandir)

 

Cela donne à peu près cela :

image

 

Et la sécurité ?

Bonne question. Mais j’y répondrai dans un prochain article.

 


Et concernant la session des JSS 2013, les Slides et vidéo seront bientôt disponibles, suivez lkes news sur http://GUSS.Pro ou Twitter (@GUSS_FRANCE).

Déplacer une arborescence de packages SSIS entre serveurs

Le problème que j’ai rencontré aujourd’hui, c’est : “comment migrer tout un projet SSIS d’un serveur à un autre”.

Je suis en SQL Server 2008 R2 et donc je ne bénéficie pas de la notion de « projet » dans le catalogue SSIS. J’ai donc plus de 200 packages répartis dans une arborescence à déplacer.

Ces packages ont été déployés tout au long de la vie du projet et en tant qu’admin, je ne souhaite pas solliciter l’équipe de développement pour me packager un script de livraison qui reprendrait tous les packages.
Après tout, le changement d’un serveur pour un autre, c’est une opération de DBA, pas de développeur donc je dois trouver une solution.

Il y a un utilitaire fourni avec SQL Server qui permet de manipuler des packages : DTUTIL.exe

Il permet de batcher des opérations sur un catalogue SSIS (2008 / 2008R2) donc cela semble une bonne piste.

Pour déplacer un package, la ligne de commande ressemble à cela :

dtutil /sql \chemin\package /SourceServer SERVSOURCE /copy SQL;\ chemin\package /DestServer SERVDEST

Le problème, c’est qu’il faut que j’écrive plus de 200 fois cette ligne de commande, avec les bons chemins.

Comme je suis faignant, je vais passer par les méta-données de MSDB et en particulier avec les tables sysssispackages et sysssispackagefolders.

Il va falloir faire du hiérarchique pour reconstruire l’arborescence, pour cela, un bonne CTE des familles (cc Philippe).

Voici le script :

;with PackagesHierarchy(folderId, ParentFolderId, FolderPath) as 
(
  select folderid, cast(null as uniqueidentifier), 
    cast('\'+foldername as varchar(max)) as FolderPath       
  from sysssispackagefolders
  where foldername = 'ProjectFolder'

  union all
  
  select sysssispackagefolders.folderid, sysssispackagefolders.parentfolderid,
   CAST(PackagesHierarchy.FolderPath +'\'+ sysssispackagefolders.foldername as varchar(max)) as FolderPath
  From sysssispackagefolders
  Inner Join PackagesHierarchy
    on sysssispackagefolders.parentfolderid = PackagesHierarchy.folderid 
)

Select 'dtutil /sql ' + FolderPath + '\' + name + ' /SourceServer ServSRC /copy SQL;' + FolderPath + '\' + name + ' /DestServer ServDEST'
From sysssispackages
Inner Join PackagesHierarchy
  On PackagesHierarchy.folderId = sysssispackages.folderid

Vous copiez tout cela dans un fichier BAT et let’s go !

Conclusion

Certains diront qu’il est préférable de déployer en mode Fichier. Moi, je ne prends pas parti Clignement d'œil

Enjoy.

Paramétrer à l’exécution le logging des packages parent et enfant

–merci à Stéphane de mon équipe qui a pris le temps de troubleshooter le problème remonté par un lecteur et surtout d’en faire une KB–

Introduction

Un package parent appelle un package enfant. Chacun déclare son LogProvider avec sa configuration. La configuration du package enfant est récupérée dans le package Parent.

Etant donné que l’initialisation du logging SSIS s’effectue avant les événements de validation et de chargement de la configuration, comment peut-on faire pour paramétrer à l’exécution le provider utilisé par le logging parent et enfant évidemment ?

Situation

Soit un package « parent.dtsx » qui appelle via une tâche « Execute package Task » un package « child.dtsx ».

clip_image001clip_image002

Nous souhaitons logguer les événements parents et enfants dans un fichier définit à l’exécution.

Solution

Pour faire court, il ne faut pas mettre les Log Provider au design-time mais à l’exécution via une option de DTExec (ou du SQL Agent, c’est pareil). Par contre, la chaine de connexion du log doit exister dans le package.

Configuration des packages

Désactiver le logging du package parent et des packages enfants (Menu SSIS > Logging)

Dans le package parent, ajouter un gestionnaire de connexion (Connection Manager) correspondant au type de log souhaité : File Connection pour logguer dans un fichier, OLEDB connection pour logguer dans une base de données, etc.

Dans notre exemple, le File Connection Manager « logging.txt » est ajouté. Il sera la destination de nos logs.

clip_image004

Une variable nommée « loggingVar » est ajoutée dans le namespace du package parent.

clip_image005

Dans les propriétés du connection manager logging.txt, une expression est créée mappant la propriété « connection string » du connection manager vers la variable loggingVar.

clip_image006

Exécution

L’exécution du package se fait avec la commande dtexec standard en ajoutant le flag de logging et en précisant son provider.

 dtexec /f parent.dtsx /L "DTS.LogProviderTextFile;logging.txt" /SET \package.Variables["User::loggingVar"].Value;parent_and_child.log 

Dans cet exemple, le log est placé dans le fichier "parent_and_child.log" situé dans le même répertoire que le package parent.

La commande correspond à :

  • /f: le package (du système de fichier) à exécuter : parent.dtsx
  • /L : active le logging. L’argument suivant détermine le provider et le Connection Manager utilisé pour le logging séparé par un point-virgule. Dans notre exemple, il s’agit du provider pour fichier DTS.LogProviderTextFile et du Connection Manager logging.txt.
  • /SET : pour affecter une variable de package. La variable affectée est loggingVar.

L’affectation de la variable par le SET se fait avant l’initialisation du logging dans ce cas. Le Connection Manager est bien affecté au fichier parent_and_child.log, puis le logging s’initialise sur le provider fichier texte sur le Connection Manager.

Le LogProvider fourni à DTExec est bien transmis aux enfants.

Le fichier de log obtenu est le suivant :

clip_image008

Pour logguer dans une base de données, il faut changer le connection manager « logging.txt » par un connection manager de type adéquat (oledb connection manager, par exemple) et changer dans la commande dtexec le provider par DTS.LogProviderSQLServer.

Encore une fois, tous les honneurs à Stéphane.

DTSConfig bug by design

(ou “Le post de l’After Gameworks Party”)

configuration dtsconfig ssis

 

Un post qui est bloqué dans ma stack depuis quelques semaines déjà.

 

Le sujet est simple, il traite de l’ordre de prise en compte de la configuration d’un package SSIS.

Si vous voulez configurer une variable dynamiquement sur votre environnement d’exécution, plusieurs choix s’offrent à vous :

  • le mettre en dur dans votre package avant déploiement (beurk)
  • utiliser un fichier de configuration (.dtsconfig)
  • utiliser une configuration qui vient d’une base de données
  • utiliser une directive /SET à l’exécution (disponible dans SQL Agent et en ligne de commande)

Note : Personnellement, j’utilise principalement le dernier choix.

Le problème vient de l’utilisation d’un DTSconfig. Prenons un exemple :

  • dans votre package vous référencez un fichier de configuration qui se trouve dans c:\config-dev.dtsconfig
  • en passant en production, vous indiquez dans le SQL Agent votre configuration de production c:\config-prod.dtsconfig
  • en production vous avez quand même un fichier config-dev.dtsconfig à la racine de C:
  • A l’exécution, c’est le fichier de configuration de DEV qui est pris en compte !

Mais pourquoi ? Regardons un peu la documentation :

Using the /ConfigFile option to load run-time configurations for the same property values that are also set by design-time configurations : Has no effect. The new values are overwritten when the utility reloads the design-time configurations after it applies the run-time options.

Ce paragraphe est tiré d’une page appelée Behavior Changes to Integration Services Features in SQL Server 2008 R2.

En fait, depuis 2008 R2, dtexec recharge le fichier se trouvant dans le chemin initial (celui utilisé pendant le développement dans BIDS) après avoir chargé tous ceux qu’on lui donne explicitement.

Oui, c’est absurde mais c’est By Design.

Evidemment, si le fichier config-dev.dtsconfig ne se trouve pas en production, aucun problème mais si vous travaillez avec la même arborescence (lecteur compris) ou si vous travaillez avec des UNC, soyez très vigilants.

Ni moi ni aucun de mes clients n’ont rencontré de soucis mais Christophe y a été confronté chez un de ses clients.

Annexe :

Dans SQL Server 2012, si vous migrez vos package sous le nouveau mode d’exécution, il n’y a plus de DTSConfig, donc plus de souci.

Oui, j’ai bien dit qu’en 2012 il n’y a plus de DTSConfig. Venez voir ma session sur SSIS 2012 aux Journées SQL Server pour en savoir plus.

 

Pour l’anecdote, ce poste vient d’une discussion très tard après une soirée du SQL PASS Summit dans la chambre d’hôtel que je partageai avec Christophe. Cette discussion s’est vite transformée en débat animé mais se termine sur la conclusion suivante : ce n’est pas un bug puisque c’est documenté, c’est donc une feature…by design Sourire

 

PS : tu vois Christophe que j’ai blogué sur le sujet.