SSIS

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.

Les nouveautés “méconnues” de SSIS 2012

Speaker : Matt Masson (équipe de développement SSIS et camarade d’ATE)

Je suis arrivé et Matt présentait déjà un Data Flow. Je n’ai pas tout compris car il a fallu accrocher les wagons mais en vrac :

  • Facebook Source : nouveau composant, 3rd party ou développement custom ?
  • composant StreamInsight : utilisation de StreamInsight pour détecter des patterns directement dans un flux SSIS; j’adore mais je ne sais pas d’où il l’a sorti
  • composant MDS :  utilisation des informations de vos référentiels MDS (ici un Lookup)

La session a ensuite démarré… c’était juste pour occuper l’audience en attendant. Ce qui fait que je ne sais pas si ce sont des futures fonctionnalités, de la RTM, du SP1, de la prochaine version…

Place aux fonctionnalités annoncées pour la RTM

Je ne vais pas revenir sur les grandes améliorations de SSIS sauf par ce court résumé :

  • amélioration du développement : corrections des bugs, nouveaux designers, nouveaux composants, etc.
  • amélioration de la production : serveur SSIS, notion de projets, logs automatiques, etc.

Je vous laisse consulter mes précédents posts et les articles complets que je vais faire sur le sujet.

Je vais en revanche pointer les fonctionnalités présentées en session et il y a de quoi faire. Tout n’est pas dans la CTP3 donc je ne peux pas vous faire de démo.

 

Change Data Capture

Pas besoin de présenter le CDC apparu en 2008 (j’avais fait un article sur le sujet, il est encore dispo ici) ? En 2 mots, c’est un moyen d’obtenir rapidement les modifications d’une table dans un intervalle de temps. Les modifications sont stockées dans des tables systèmes et, à la différence de la vieille méthode des triggers, c’est le moteur qui gère tout.

Tout cela est très utile pour un ETL qui doit transférer ces modifications dans un DataWarehouse (ou tout autre système qui en aurait besoin).

Avec SQL Server 2012 arrive le CDC pour Oracle et le support du CDC dans SSIS

  • CDC Oracle : un service Windows (et une application de gestion) s’occupe de récupérer les changements depuis une base Oracle
  • SSIS CDC Control Task : permet d’effectuer des opérations sur le CDC comme récupérer les bons LSN
  • SSIS CDC Source : permet de récupérer sous la forme d’un flux de données les modifications de données
  • SSIS CDC Splitter Transformation : comme le composant SCD, il permet de différencier dans des flux différents les ajouts, les modifications et les suppressions

Tout cela rend le CDC encore plus attrayant et je suis content de l’ajout de ces composants.

 

ODBC source/destination

Avec l’arrêt d’OLEDB et un revirement vers ODBC, il est évident que les composants sources et destinations allaient aussi suivre le mouvement.

Microsoft annonce de meilleures performances sans grandes explications. J’attends de voir car ça ressemble plus à la stratégie “je m’accroche aux branches”.

En tout cas, les composants sont là et ils fonctionnent Sourire.

 

Shared Connection Manager

J’en avais déjà parlé, la gestion des connexions a été grandement améliorée. On peut promouvoir une connexion au niveau projet (et inversement), mettre offline une connexion, etc.

Lors de la session, j’ai pu voir des petits icones apparaitre pour indiquer si une connexion utilise une expression ou si elle est offline.
Cela ne marche que pour les connexions malheureusement. Il vous reste BIDSHelper qui apporte ce genre de confort de façon générale.

 

Flat Files

Je n’ai pas encore testé et la démo a été extrêmement rapide mais 2 améliorations arrivent sur les fichiers plats :

  • Meilleure gestion des Text qualifier (quand ils sont imbriqués)
  • Nombre de colonnes variable (ie. meilleure gestion des séparateurs)

 

Code XML

Il est maintenant beaucoup plus propre et plus court. Il est enfin lisible ; des nœuds ont été transformés en attributs et le positionnement a été revu et placé à la fin.

Cela permet une chose : le Merge de packages

Enfin on va pouvoir travailler à plusieurs sur le même DTSX, en multiple checkout dans TFS avec un merge de fichiers XML lisible.

Attention, ce n’est pas non plus trivial et des erreurs sont encore possibles.

 

Debug des Script Components dans le Data Flow

Je crois que tout est dit, sauf peut-être : merci Sourire

 

Pilotage via Poweshell

La gestion du serveur (création, exécution, administration) se fait entièrement avec des procédures T-SQL. Cependant, l’équipe SSIS a développé une API en .NET au dessus (qui lance donc du T-SQL avec ADO.NET) et cette API est Powershell-isable.

Donc vous pouvez administrer, déployer, gérer votre serveur SSIS en PowerShell.

 

Data Viewers –> Data Taps

Les Data Viewers que vous mettez en développement pour voir ce qui passe entre les composants sont maintenant disponibles à l’exécution sur le serveur et s’appellent des Data Taps.

En plus des logs automatiquement gérés par le serveur, il est possible (pour l’administrateur uniquement) de placer ces Data Taps sur les Paths (ex : \Package\aDFTtask\Paths[SourceSQL.ConversionDate]) de vos Data Flow.

Les données sont dumpées dans un fichier CSV et rassurez vous, il est possible de limiter les colonnes et le nombre de lignes exportées.

 

Integration Services Dashboard

Dans Management Studio, il y a depuis quelques temps déjà des rapports sur les différents éléments aidant grandement à l’administration (mon préféré est Disk Usage Sourire).

Avec le logging automatique du serveur SSIS, SSMS nous offre des tableaux de bord qui exploitent ces données.

On peut donc en quelques clics voir, entre autres :

  • les packages exécutés
  • les packages en erreur
  • les historiques d’exécution
  • les temps d’exécution des différentes partie (avec historisation)
  • etc.

 

Correlated Logging

Afin de profiter des logs automatiques avec votre propre log métier (custom logging), vous avez à disposition une variable (je ne me souviens plus du nom mais c’est du genre @User::BatchID) pour pouvoir corréler par la suite les différents logs.

Dependency Services // Projet Barcelona

Pour finir, je vais parler brièvement du projet Barcelona.

J’ai pu faire des démos (MS Days Toulouse) et parler de Dependency Services sorti avec de la CTP1. Dependency Services est sorti de Denali avec la CTP3 pour vivre sa propre vie.

L’idée reste la même : tracer toutes les dépendances des données entre les composants d’une solution BI (sources, SQL, packages SSIS, rapports, modèles PowerPivot, etc.).

L’objectif est de pouvoir répondre à des questions comme : que ce passe t-il si je supprime cette colonne ?

Je suis maintenant en relation avec l’équipe de développement car j’adore le projet et donc je ne vais pas tarder à vous en parler plus en détail.

Le projet est prévu pour sortir juste après SQL Server 2012 et en attendant, suivez ce blog ou celui de l’équipe : http://blogs.msdn.com/b/project_barcelona_team_blog/

BIDS 2010

Une feature très attendue pas les développeurs décisionnels, c’est l’alignement des outils de développement avec Visual Studio 2010 ;  plus besoin d’installer les 2 versions.

Avec cet alignement, on voit quelques améliorations notoires du côté de SSIS. Je suis pourtant fan de cet ETL mais je dois reconnaitre que certains assistants ne sont pas des plus aboutis ou compliqués à utiliser.

La première amélioration est très visuelle. On la remarque dès l’ouverture d’un package : le rendu graphique est grandement amélioré.

image

Cela est dû au designer développé en WPF dans cette version. C’est plus joli (les icônes ont été un peu relookés) et il est possible de zoomer (car le rendu est vectoriel).

 

Autre amélioration que j’ai grandement apprécié dès l’ouverture d’un package, c’est une meilleure gestion des connexions.

On a tous un jour maudit SSIS parce qu’il teste les connexions à l’ouverture d’un package. 
Le comportement est normal sauf que SSIS teste chaque connexion de chaque composant et attend la fin du LoginTimeout pour s’apercevoir que la base ou le serveur n’est pas accessible.
Sur un gros package, cela peut prendre plusieurs minutes.

Les développeurs SSIS connaissent donc le mode offline mais qui désactive toute la connectivité du projet.

Avec Business Intelligence Developement Studio  2010, la connectivité est testée une bonne fois pour toute à l’ouverture (si le serveur ne ping pas, la main est rendue aussitôt) et il est possible de mettre Offline juste une connexion.

image[7]

Autres améliorations que j’ai noté en vrac dans le développement de package DTSX :

  • le Ctrl+Z enfin implémenté
  • les annotations avec un retour à la ligne automatique
  • les tooltips précisant les types dans le composant de Lookup
  • l’ajout de “groupes” dans les Data Flow Task
  • la possibilité de promouvoir une connexion au niveau projet

Je ferai un post (voire un article complet) sur le moteur SSIS qui voit de nombreuses améliorations importantes.

 

Migration

Avant de terminer, quelques mots sur la migration de package.

J’ai converti 2 projets SSIS 2008 sans problème. J’ai rencontré un effet de bord dans quelques Data Flow Task où le designer n’arrive pas à positionner les composants (donc tout se cale en haut à gauche).

Mon Lookup ne matche plus

Un nouveau post rapide pour une problématique que vous pouvez rencontrer avec les Lookup Transformations dans SSIS.

Problème : les données semblent égales et pourtant le Lookup n’arrive pas à matcher. Fait perturbant, si vous passez le mode de cache en No Cache, ça marche…

image

Solution :

Le problème vient des types de données. Dans mon exemple, j’ai un NUMERIC(6,0) d’un côté et un NUMERIC(18,2) de l’autre.

En mode cache, c’est SSIS qui fait la comparaison et sa représentation interne dans son cache et ses buffers est fortement liée par les types. Ainsi la comparaison “binaire” (certes optimisée) échoue et les données ne matchent pas.

En mode “No Cache”, c’est SQL Server qui fait la comparaison et là intervient un transtypage (CAST) implicite. Dans notre exemple, la comparaison d’un NUMERIC(6,0) et d’un NUMERIC(18,2) fonctionne et c’est pour cela que le Lookup matche les données.

Mon exemple utilise des types numériques mais on a le même problème avec les chaines de caractère en fonction de la COLLATION (et du Codepage sur SSIS).

PS : notez également que le Lookup est Case Sensitive !

Conclusion :

Soyez vigilant sur les types et essayez de coller au mieux (pour faire simple, utilisez exactement les mêmes).

SSIS – Erreur –1071607685 – No Status Available

 

Dans vos packages Integration Services, si vous êtes confrontés à cette erreur… :

Error Code : –1071607685
Error Column : 0
Error Desc : No Status Available

…ne cherchez pas pendant des heures. Regardez en premier lieu dans les composants destination et désactivez les options Fast Load.

FastLoad

Cette option fait une insertion en mode BULK et par défaut en batch de 10 000 enregistrements. Si au moins l’un de ceux là est en erreur (doublon, clé étrangère manquante, valeur invalide, etc.), c’est le batch qui part dans le flux d’erreur et aucune description d’erreur n’est disponible.

La première chose à faire pour déboguer est de désactiver l’option pour avoir des INSERT ligne à ligne. Vous verrez donc l’erreur qui fait planter le batch.

Mais alors comment faire dans la vraie vie. Plusieurs idées :

  1. Dans le cadre d’un ETL de DataWarehouse, il faut faire toute les vérifications AVANT l’insertion (c’est aussi une des raisons pourquoi on ne met pas de Foreign Key). Votre DataFlow doit garantir la validité des données (et prendre les actions correctrices nécessaires)
  2. Utiliser un paramètre DEBUG pour désactiver l’option à la volée en production (il faudra passer par une variable et un conditional split)
  3. Utiliser la technique du double-essai. On fait d’abord une première insertion en mode FastLoad, on redirige la sortie d’erreur sur un INSERT normal. Ainsi, dans (normalement) la majorité des cas, vous ferez du Bulk et le deuxième essai permet de limiter les dégats d’un batch qui part à la poubelle (et surtout vous donnera les détails de l’erreur).