Parution de Integration Services 2012 chez ENI en Novembre!

Voilà vous savez maintenant à quoi ma capacité rédactionnelle a été occupée ces derniers mois: en effet, le mois prochain si tout se passe bien vous pourrez trouver chez tous les bons libraires techniques – pas la peine d’essayer chez votre vendeur de BD préféré – le livre Integration Services 2012 que j’ai co-écrit avec mes gentils collègues Patrice Harel et Romuald Coutaud.

Tout au long des onze chapitres, vous découvrirez pour ceux qui ne connaissent pas le développement, l’administration et l’extension du produit. Le livre s’adresse aussi bien aux débutants qu’aux utilisateurs du produit souhaitant étendre leurs connaissances, puisque nous avons essayé d’être les plus exhaustifs possibles tout en restant abordables.

Merci à Romuald et Patrice, et à tous ceux qui nous ont aidé durant la longue gestation de cette “oeuvre” majeure à n’en pas douter!

Publicités

« Les procédures stockées ne sont pas des vues paramétrées » Vraiment en SQL 2012?

L’excellent Adam Machanic a publié en 2006 un post qui a fait date dans la compréhension des procédures stockées, et particulièrement dans un contexte OLE DB (qui a dit SSIS?). Ce post s’appelait Stored procedures are not parameterized views et je vous conseille de le lire si ce n’est déjà fait. Il y explique la raison profonde de la non exposition classique de métadonnées dans les procédures stockées: elles sont non prédictibles et peuvent renvoyer des données très différentes selon la branche – au sens algorithmique – dans laquelle le retour s’effectue. Donc il n’y a pas de stockage dans les catalogues C’est un gros problème pour OLE DB par exemple, qui dans un vaste ensemble de cas est incapable de comprendre les méta associées et complique leur usage comme source, d’un rapport, d’un Data Flow ou autre.

Comment cela marche alors dans SSIS?

SQL Server propose un mode d’exécution appelé FMTONLY ON. Si vous regardez Books Online, vous verrez que cette instruction permet effectivement une inspection des chemins de code et le retour explicite de métadonnées seules dans un objectif de validation. C’est une opération assez empirique, et qui pour être honnête marche parfois assez mal: elle ne gère pas les tables temporaires, pas les curseurs… C’est potentiellement  par exemple ce qui est assez handicapant. Ce post est assez connu sur le sujet des tables temporaires et propose des workarounds qui se valent…

Quelle est la parade aujourd’hui?

En réalité il y en a plusieurs. Une des solutions consiste à désactiver ce FMTONLY pour que SSIS exécute réellement pour extraire les métas. Et effectivement cela fonctionne. C’est la technique bien connue de pas mal de devs, mais elle a ses inconvénients aussi car elle cause de sévères problèmes de performances (SSIS valide constamment les sources, et FMTONLY ON est quand même plus économe qu’une exécution complète).

Il y a aussi la parade qui consiste à faire pas mal de refactoring de code dans le T-SQL pour faire marcher FMTONLY ON. Ce qui n’est pas franchement l’objectif souhaité quand on veut faire du couplage faible entre l’ETL et ses sources… Jamie Thomson en était arrivé à la conclusion dans ce post que les PS ne sont pas faites pour ça… et que dans pareil cas les UDF sont là. Je suis assez d’accord avec lui. Sauf que parfois on ne me laisse pas le choix et je dois quand même utiliser des PS.

Qu’apporte SQL Server 2012?

Il apporte la possibilité d’exécuter une procédure stockée en spécifiant le contrat attendu. Les métadonnées exposées sont celles que vous spécifiez. Si le contrat est enfreint alors l’exécution échoue. Ecrivons une requête bien inutilisable, avec une belle table temporaire pour SSIS 🙂

CREATE PROC usp_GetTopCustomersForYear(@Year INT)
AS
 SET NOCOUNT ON
 --Une requête toute bête dans une table temporaire
 SELECT TOP 10 c.CustomerAlternateKey, SUM(s.SalesAmount) SalesAmount
 INTO #TopCustomers
 FROM AdventureWorksDWDenali.dbo.DimCustomer c
 JOIN AdventureWorksDWDenali.dbo.FactInternetSales s
 ON s.CustomerKey = c.CustomerKey
 JOIN AdventureWorksDWDenali.dbo.DimDate d
 ON d.DateKey = s.OrderDateKey
 WHERE d.CalendarYear=@Year
 GROUP BY c.CustomerAlternateKey
 ORDER BY SalesAmount DESC
 --Un SELECT depuis cette table
 SELECT *
 FROM #TopCustomers
GO

Essayons de l’utiliser dans SSIS: échec. Le message d’erreur indique bien que FMTONLY est incapable de parser les métadonnées d’une PS qui fait usage d’une table temporaire.

Là ou je me lancerais maintenant dans un refactoring fastidieux, je vais modifier simplement l’appel à la procédure stockée. Je vais juste spécifier ce que j’attend.

EXEC usp_GetTopCustomersForYear ?
WITH RESULT SETS
(
   (
      CustomerKey NVARCHAR(15),
      Amount FLOAT
   )
)

Le résultat est bien meilleur à tous points de vue!

Il est à noter que ce WITH RESULT SETS peut être utilisé à beaucoup d’autre endroits, pour permettre du couplage très faible entre les applications et les procédures stockées: cela va par exemple permettre de caster les types castables dans un type précis attendu, plutôt que d’être potentiellement embêté par des providers pointilleux sur le typage en cas de modification de la PS par le DBA.

Et pourquoi ne pas avoir directement permis d’exposer les métas des procédures stockées?

Parce que sinon on appellerait ça des UDF. Blague à part ce n’est pas la volonté de Microsoft, la séparation entre procédure et fonction, que les vieux codeurs Pascal connaissent bien aussi, reste dans SQL Server 2012.

D’autres choses?

Oui, on peut noter aussi pour remplacer FMTONLY de nouvelles DMV toutes propres font leur apparition: elles permettent d’exposer des catalogues de métas renvoyés par des procédures stockées si cela est possible. Mais elles partagent avec FMTONLYdes inconvénients. Ma PS n’est pas reconnaissable par ce statement, qui renvoie invariablement:

The metadata could not be determined because statement ‘SELECT * FROM #TopCustomers’ in procedure ‘usp_GetTopCustomersForYear’ uses a temp table.

SELECT *
FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('dbo.usp_GetTopCustomersForYear'),1)

Voilà pour le sujet, en espérant que cela vous permette d’exécuter des PS en source plus facilement. A bientôt!

CEP vs ETL, StreamInsight et SSIS dans la BI


Je suis de retour du MVP Summit (avec un très bel event au Safeco Field de Seattle que vous voyez ici) et à nouveau chez mon client préféré! Hormis vous dire que ce que nous avons appris augure de bien belles choses sur SQL Server Denali en général, et sur ses modules BI en particulier, ne comptez pas trop sur moi pour aller plus loin dans les détails car j’ai signé un NDA avec mon sang…

Par contre plus sérieusement j’ai été pas mal intéressé là bas par les scénarios de convergence des différents produits sortis ou à venir. Il est vrai qu’après une grosse phase de simplification marketing autour de briques bien identifiées (SQL Server 2005), l‘offre BI à l’horizon Denali devient de plus en plus complexe à lire pour nos chers clients et utilisateurs. Par conséquent la recherche à minima de convergences entre les différents modules est un sujet intéressant.
Intéressant techniquement pour adresser plus de problématiques, intéressant pédagogiquement car c’est de notre faculté à rendre cette offre lisible que dépendra en grande partie l’adoption des ces produits.
Le positionnement du CEP StreamInsight par rapport à l’ETL SSIS est un bon exemple de cela.
ETL et CEP dans la BI

Un ETL (Extract Transform Load) comme SSIS est le produit traditionnellement utilisé pour charger les entrepôts de données ou data warehouses. Orienté batch (ensemble de traitements bien identifiés) et planifié à une certaine fréquence, il est utilisé pour aller chercher les données dans les bases opérationnelles, et les amener dans les bases d’analyse, non sans leur avoir appliqué un nombre généralement considérable de vérifications, aggrégations, filtrages et autres consolidations.
Depuis quelques années et l’avènement du « mythe » (entre guillemets car il devient petit à petit réalité) des entrepôts de données (near)real-time ou zero-latency illustré côté Microsoft par des features telles que le Proactive Caching dans SSAS (pour ne citer que lui), on parle d’une supplantation des ETL par des CEP, des moteurs de Gestion d’Evenements Complexes (Complex Event Processing).
Un CEP est un moteur d’analyse de flux (souvent) constants de données, à la recherche de singularités, ou simplement à des fins d’aggrégation.
StreamInsight, sorti avec SQL Server 2008 R2 est le CEP de SQL Server, il s’agit d’un moteur utilisable en .NET dont les règles de traitement et d’agrégation et l’expression de KPI s’écrivent par des requêtes Linq sur les flux entrants, donnant des possibilités quasi infinies en terme de transformations, le tout en temps réel. Le pas est vite franchi pour certains oracles du décisionnel: ils prédisent que les ETL et les bases temporaires feront bientôt partie du passé.

Remplacer SSIS par StreamInsight dans l’alimentation d’entrepôts?

Soyons brefs: à date et sauf pour des ultra particuliers, il n’en est évidemment pas question, en tous les cas aujourd’hui, et ce pour plusieurs raisons:
1) Pas de composants spécialisés ETL: StreamInsight pour le développeur est un framework: les composants de DataFlow que l’on est amenés à utiliser en ETL avec SSIS seraient à recoder, ainsi que les interfaces d’entrée, de sortie…
2) Trop orienté développeur: utiliser StreamInsight à la place de SSIS aujourd’hui est aussi aisé que d’utiliser SSIS uniquement par le biais de son API de génération de packages… Disons pas aussi simple qu’avec un designer graphique.
3) Administration limitée: là encore les fonctionnalités out-of-the-box de StreamInsight sont restreintes car répétons-le ce n’est pas son usage premier: un CEP doit traiter des données en masse et peut se permettre des erreurs: un entrepôt contient la version corporate de la « vérité »: il n’y a pas de droit à l’erreur la supervision doit être facile et les erreurs aisément traçables.
Au bilan je ne crois pas que sauf pour des tables de faits spécifiques, ou des entrepôts dédiés à la latence zéro on n’assiste avant quelque temps à la gestion de l’ETL sous SI. En revanche Microsoft est tout à fait conscient de l’intérêt suscité par certaines potentialités de StreamInsight pour les développeurs ETL…
Avant la fusion, des scénarios de convergence…

StreamInsight permet en effet de requêter des flux en Linq: quelle puissance cela donnerait de pouvoir faire les mêmes requêtes sur des PipelineBuffer, ce qui revient en effet à embarquer un moteur StreamInsight dans SSIS.
A l’inverse, pour adresser l’absence de composants dans StreamInsight ou pourrait nourrir des lots SSIS à partir de données issues de CepStream, scénario inverse ici puisque cela revient à embarquer un lot SSIS dans un flux StreamInsight.
Ping Wang et Wee Hyong Tok des équipes StreamInsight et SSIS ont écrit un WhitePaper inspiré comme souvent de retours clients sur ce type d’usages de ces deux produits couplés: vous pouvez le trouver ici.
En espérant avoir clarifié quelques points et vous avoir donné quelques idées.
A bientôt!

[SSIS] Concaténer les valeurs d’une ligne de données

Petit besoin intéressant: vouloir reproduire le fonctionnement des sources de Data Flow en concaténant les valeurs de toutes les colonnes d’une ligne de Pipeline dans une colonne unique (à des fins de log par exemple, pour tout mettre dans une seule table SQL).

La technique n’est pas très compliquée, il suffit de savoir comment s’y prendre au départ.
Tout d’abord il faut que le composant soit dynamique: cela veut dire que le script doit marcher pour tout buffer d’entrée. Cela élimine immédiatement la méthode ProcessInputRow(ScriptBuffer) au détriment de ProcessInput(PipelineBuffer). Il est en effet impossible d’accéder à une ligne d’un ScriptBuffer via une méthode générique, il faut utiliser des accesseurs qui portent le nom de la colonne, que par essence on ne connaît pas (et j’annonce: la Reflection n’est pas une option ici, même si la première fois que j’ai codé ce script je l’ai fait comme ça…).
Donc pour travailler avec un PipelineBuffer il faut connaître l’index de la colonne à récupérer: bonne pioche, le Script Component bénéficie d’une méthode GetColumnIndexes() qui renvoie les indexes des colonnes d’un Input100 ou Output100. Une fois ces indexes récupérés, on a plus qu’à concaténer avec un StringBuilder.
Le code (simplissime) est ci-dessous (à noter que je n’ai qu’une seule colonne dans mon Output100: ma valeur de concaténation).

public class ScriptMain : UserComponent
{
private int[] columnIndexes;
private int outputColumnIndex;
public override void PreExecute()
{
base.PreExecute();
columnIndexes = this.GetColumnIndexes(this.ComponentMetaData.InputCollection[0].ID);
outputColumnIndex = this.GetColumnIndexes(this.ComponentMetaData.OutputCollection[0].ID)[0];
}

private string RowConcat(PipelineBuffer b, int[] columnIndexes, char separator)
{
StringBuilder sb = new StringBuilder();
foreach (int columnIndex in columnIndexes)
{
if (b[columnIndex] != null)
sb.Append(b[columnIndex].ToString());
sb.Append(separator);
}
return sb.ToString();
}

public override void ProcessInput(int InputID, PipelineBuffer Buffer)
{
while (Buffer.NextRow())
{
Buffer.SetString(outputColumnIndex, RowConcat(Buffer,columnIndexes,';'));
}
}
}

[News] Premières impressions sur Denali CTP1 et SSIS

Je joue en ce moment avec la CTP1 de Denali. Hormis les nouveautés relationnelles très sympa (je pense au EXEC WITH RESULT SETS) que Christian Robert a très bien résumé, c’est surtout Integration Services qui bénéficie du plus gros lifting.
Enfin un designer du XXIème siècle!

BIDS dans cette CTP est encore sous VS 2008 mais le designer est maintenant en WPF! C’est l’occasion de profiter de quelques améliorations:
– C’est beaucoup plus joli, fluide, et à mon avis plus stable (bye bye COM!). L’avantage de l’abandon de MSDDS pour le diagraming est de rendre le XML de « rendu » beaucoup plus lisible et surtout parsable. Par exemple il sera possible de récupérer les commentaires (ça vous rappelle probablement un de mes posts…) puisqu’ils sont maintenant écrits en clair (enfin en XML commenté, sous forme de noeud AnnotationLayout). Sans compter que ça apporte un truc incroyable: on peut maintenant faire CTRL-Z ou CTRL-Y: une révolution!

– La toolbar est moins linéaire et permet de gérer des favoris, elle relègue les composants les moins utilisés dans des dossiers Others.
-Côté Data Flow, c’est aussi pas mal. Là aussi la toolbar est classée, des wizards apparaissent pour créer les adapteurs source et destination, mais en plus de ça on peut éditer les composants déconnectés, et pas en mode avancé. Si on rajoute la connectivité avec Data Quality Services c’est assez sympathique.
Des nouveautés aussi pour le déploiement
Dans le domaine du management des packages le déploiement et la partie serveur ont été complètement revus. (Ceci dit au passage, impossible de se connecter pour moi sans passer par l’UAC, cf ici).
Côté serveur de base, une node Integration Services apparait, permettant de créer un Catalog SSIS, en réalité une base, nommée par défaut SSISDB (et remplaçant donc MSDB, les DBA seront contents). C’est maintenant ici que se gèrent les lots SSIS en v11 et non plus dans le service auparavant dédié.
D’un point de vue logique, ce catalog comprend en suite des Projects: finie la structure bordélique des lots arrangés dans une foultitude de dossiers logiques. De plus ces projets sont nativement versionnés! (A l’assaut de TFS!!!)

De plus ce ne sont plus réellement les Dtsx que l’on déploie: comme l’a montré Jamie Thomson le build des projets SSIS génère une sorte de binaire (un fichier .ispac) dans un dossier /bin, qui est en fait ce qui est déployé. Comme le dit Jamie, ça commence sacrément à ressembler à du développement et plus trop à du vieux DTS… Et c’est tant mieux.
Côté BIDS, on peut maintenant, et sans BIDS Helper déployer directement depuis le projet, le réglage CreateDeploymentUtility disparaissant au profit du nom du serveur SSIS, là encore comme dans l’addin cité précedemment (et aussi AS, RS au passage). Il suffit dès lors dans un wizard de renseigner le Catalog, et le Project.
Les packages comprennent de plus des paramètres, des variables renseignables depuis le wizard de déploiement (et en l’état bindables à des variables d’environnement).
Ceci évite d’avoir à saisir des valeurs de variable type Package.Variables[XYZ].Value comme on devait le faire auparavant. De plus ces paramètres sont ensuite librement utilisables comme des variables dans le lot.
Une petite note sur Dependency Services

La nouvelle fonctionnalité Dependency Services, de suivi des relations et des modifications dans les bases de données est étendu à SSIS: il est donc possible de visualiser les dépendances d’un package vis à vis de certaines bases. Dit autrement savoir rapidement quel serait l’impact de la modification d’un champ dans votre Data Mart sur les lots d’alimentation, les autres tables…
J’attends de creuser un peu mais cela sort complètement la partie serveur de la boite noire où elle était confinée, et surtout cela permettrait d’avoir une vraie analyse d’impact au niveau de la stack BI.
En espérant vous avoir donné envie de l’installer!
A bientôt!

[SSIS] "The package path references an object that cannot be found"

Alors ce message d’erreur: simple warning ou erreur fatale? Et bien les deux…
Revenons un peu en arrière et expliquons le contexte: un fichier de configuration SSIS contient sous forme Clé/Valeur des valeurs à affecter au runtime à des propriétés du package. En 2005, le fait qu’une propriété n’existe pas dans le package mais soit déclarée dans le fichier de configuration générait un petit warning de rien du tout.
En 2008, un appel à DTEXEC avec en paramètre ce type de fichier de configuration générait une erreur de chargement fatale. Résolu en SP1 CU4, ce bug – il faut donc l’appeller ainsi – a été longtemps considéré par tout le monde, moi y compris, comme une nouvelle feature, obligeant à arrêter de partager les fichiers de configuration entre les différents packages utilisant chacun des parties dédiées du fichier. Bien entendu ce « nouveau / ancien » mode de fonctionnement perdure sur R2:
Soit un nouveau Package comprenant la variable A. Cette variable comprend « A’s Default Value ». Le package comprend un unique composant script qui affiche cette valeur dans une MessageBox.
Soit son fichier de configuration, qui en plus de donner à A la valeur « A’s Configured Value » cherche aussi à configurer B qui n’existe pas.

A l’exécution on a bien un warning, mais la valeur de A a bien été affectée, comme en témoigne la MessageBox.

[SSIS] Modifier l’apparence des packages via l’API [FAIL]

Ahhhh celle là on me l’a posée très récemment, au travers de la question « Comment ajouter programmatiquement des commentaires à un package » et je dois avouer que je me suis cassé la tête des nuits dessus il y a quelques mois, à la suite de la lecture d’un thread très proche entre Jamie Thomson et l’équipe de dev.

SSIS vous le savez fournit une très belle API de design de packages, qui permet de modifier tout un tas de composants appartenant à ce beau schéma que j’affectionne:

Mais dans ce schéma et dans la doc de l’API, aucune note sur l’apparence… Comment réorganiser les tâches, ajouter un commentaire?
Ouvrons donc un package SSIS en mode code pour voir comment cela est stocké. Un package est bien un fichier XML stockant toutes ses propriétés et composants sous format XML, comme on peut le voir ci-dessous:
En revanche dans ce code XML bien propre aucune mention de l’apparence, du positionnement des éléments, des commentaires. Par contre en scrollant un peu vers le bas, on trouve une propriété de package au nom étrange:

Hmm… et cette propriété contient du code XML dont les balises > et < ont été échappées avec leur code (gt, lt). Ce code XML est très long et abscons et décrit des positions d’objets. Un commentaire, objet qui n’apparait pas dans l’api, apparaît ici sous cette forme (j’ai « deséchappé » les caractères pour que ça ressemble à du XML (le commentaire est encodé en binaire dans la propriété binary) :

<ddscontrol controlprogid=« MSDDS.Text » left= »-5172″ top= »-129″ logicalid= »38″ controlid= »20″ masterid= »8″ hint1= »0″ hint2= »0″ width= »2970″ height= »344″ noresize= »1″ nomove= »0″ nodefaultattachpoints= »1″ autodrag= »1″ usedefaultiddshape= »1″ selectable= »0″ showselectionhandles= »0″ allownudging= »1″ isannotation= »0″ dontautolayout= »1″ groupcollapsed= »0″ tabstop= »0″ visible= »1″ snaptogrid= »0″><ddsxmlobjectstreaminitwrapper binary= »000200009a0b000058010000030000000000050″ />

La première chose que l’on remarque ici c’est le nom du contrôle de commentaire, MSDDS.Text. Qu’est ce que c’est que ça?

Quelques recherches plus tard vous auriez appris que DaVinci Design Surface est une très vieille API native de design de diagrammes (tiens donc…) utilisée dans Visual Studio 5.0 et 6.0 mais ayant vocation à disparaître. La team SSIS l’aurait utilisé en 2005 faute de mieux. Il y a donc une grosse différence entre MSDDS – le code XML échappé bourré au milieu des éléments – et la structure des packages.
Un commentaire ne fait pas partie des objets de package… Too bad.
Je vais vous épargner mes digressions au Reflector et mes réouvertures de packages en interop MSDDS pour modifier les commentaires, tentatives ayant toutes abouti à créer des monstres imparsables (jouer au Dr Frankenstein n’est décidément pas rentable).
Moralité: pour le moment, pour de la documentation automatique, utilisez les champs Description des objets, c’est bien plus facile, accessible via l’API, et en plus ça tiendra probablement la migration en SSIS Denali, ce qui ne serait probablement pas le cas de mes cochoncetés en natif.
A bientôt!