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!

« 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!

[SSIS] Utiliser PowerShell pour remplacer DtUtil et le manifeste

Ceux qui lisent ce blog – merci à eux! – le savent, je ne supporte pas le manifeste de déploiement et utilise plutôt l’utilitaire DtUtil pour déployer mes packages.
Il arrive malheureusement que l’on me rétorque que le manifeste de déploiement dispose de nombreux avantages par rapport à mon utilitaire préféré.
…pour être honnête en réalité il n’en a que deux.

1) Il est plutôt joli
2) Il permet de modifier l’emplacement de la configuration dans le package

Si l’on omet le point numéro 1 (la cosmétique étant par nature subjective) on se dit que la seule chose sympatique que permet ce manifeste c’est d’éditer le lot pour changer sa node « Configuration ».

Quels seraient donc les specs de l’utilitaire « idéal »?

Il serait automatisable – ce qu’est DtUtil mais pas le manifeste – et pourrait déployer des configurations tout en modifiant leur chemin – ce que ne sait pas faire DtUtil. Il s’agirait donc d’un DtUtil capable de modifier un dtsx.

DtUtil et le manifeste supplantés par PowerShell!

L’API .NET SSIS et son objet Application permettent d’ouvrir un lot, de le modifier et de le déployer ou que ce soit (DTS, SQL, File), ils constituent donc une solution stable (pas d’édition de XML très sale) et… scriptable donc automatisable, grâce à PowerShell!

Un petit exemple

En trois temps:

1) On modifie le package

$app = new-object Microsoft.SqlServer.Dts.Runtime.Application
$pkg = $app.LoadPackage($oldPackagePath,$null)
$oldConfigPath = $pkg.Configurations[0].ConfigurationString
$pkg.Configurations[0].ConfigurationString = $newConfigPath

2)On déploie la configuration à sa destination

Copy-Item $oldConfigPath $newConfigPath

3) On déploie le package à sa destination (en fichier ici mais cela aurait pu être SQL)

$app.SaveToXml($newPackagePath,$pkg,$null)

Et voilà!

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

Mais que se passe-t-il? Ce fichier de configuration, que vous utilisiez en 2005 passait très bien en DtExex /ConfigFile – certes avec quelques warning – mais le voilà qui vous lève l’erreur suivante:

The package path references an object that cannot be found

Ce problème s’ajoute à la série des modifications de comportement de DtExec (voir aussi le fait déja abordé que les configurations définies dans le package prennent le pas sur celles de /ConfigFile)
mais il ne s’agit pas ici une feature mais bien un bug corrigé dans le CU4 du SP1 de SQL Server 2008. Si vous ne pouvez pas l’installer sachez que configurer le fichier en tant que configuration dans BIDS permet d’exécuter le package comme avant.

Source: Lien sur le support .

[SSIS] Le Data Flow et les Expressions ("Ca marchait bien dans DTS!")

L’autre jour me vient l’idée de modifier dynamiquement la propriété MinSimilarity d’un Fuzzy Lookup depuis un fichier de configuration… Et oh stupeur c’est impossible.
Parce qu’on ne peut pas tout modifier dans SSIS, ça n’est pas DTS…
Nous reparle pas de DTS!

Ah DTS… les scripts ActiveX qui permettent de modifier n’importe quelle propriété du package quel « bonheur ». En effet le modèle objet d’un lot DTS 2000 était directement accessible en écriture depuis le lot lui même. Pratique mais un tel nid à bug qu’un tout autre paradigme a été employé pour SSIS. Les assignations dynamiques de propriétés passent par des Expressions strictement encadrées, mais qui n’enlèvent pas (ou peu) de fonctionnalités de paramétrage dynamique. (Voir ce très vieux postde Jamie Thomson)


Et le Data Flow?

En ce qui concerne le Data Flow, seul le conteneur est de type « exécutable » et possède donc des expressions. Ceci dit certains attributs de ses enfants peuvent être utilisés comme expression: pour cela ils sont dupliqués en tant qu’attributs du composant Data Flow à la création des composants enfants. Par exemple un DataFlow contenant un Lookup possèdera deux propriétés supplémentaires, [NomDuLookup].[SQLCommand] et [NomDuLookup].[SQLCommandParam] qui correspondent aux propriétés avancées du Lookup. Celles ci sont donc assignables par expression..


Mais on constatera rapidement que tout n’est pas exposé… et que certaines propriétés de composants de Data Flow seraient bien pratiques en tant qu’expressions. La liste de ces propriétés settables par expression est trouvable ici sur MSDN.
Pourquoi elles ne sont pas toutes exposées…
Lorsque l’on développe une tâche de Data Flow avec le framework, pour chaque attribut, on peut choisir sa valeur d’ExpressionType. C’est elle qui définit si cette propriété sera exposée ou pas (par duplication dans le composant Data Flow parent) à l’éditeur d’expression, et qui permetrait
– de modifier une propriété par expression
– de l’adresser via une configuration
Cette propriété ExpressionType est un index de l’énumération DTSCustomPropertyExpressionType, positionné par défaut à CPET_NONE (pas settable par expression). Or pour qu’un attribut soit adressable par expression, il faut explicitement que l’ExpressionType vale CPET_NOTIFY.
Les développeurs de SSIS ont globalement setté cette propriété a plein d’endroits utiles… mais ils ont parfois oublié de l’activer.
Et pour les autres propriétés? Dans le cas du MinSimilarity du Fuzzy Lookup?

Il faut donc ruser pour contourner et ça ne sera de toute manière pas très propre. La solution, puisqu’on ne peut pas modifier le package en cours va être de… le modifier depuis un package « lanceur ».
//On instancie une application pour éditer le lot
Microsoft.SqlServer.Dts.Runtime.Application application
= new Microsoft.SqlServer.Dts.Runtime.Application();
//On récupère l’instance du FuzzyLookup via le connection Manager
string packageName
= (string)Dts.Connections[« PackageTest.dtsx »].AcquireConnection(null);
Package package = application.LoadPackage(packageName.ToString(), null);
CManagedComponentWrapper fuzzyLookup
= ((MainPipe)((TaskHost)package.Executables[« DFT »]).InnerObject)
.ComponentMetaDataCollection[« FL »].Instantiate();

//On lui affecte la valeur d’une variable en readonly
int MinSimilarity = (int)Dts.Variables[« MinSimilarity »].Value;
fuzzyLookup.SetComponentProperty(« MinSimilarity », MinSimilarity);

//On sauvegarde (cela aurait pu être en SQL…)
application.SaveToXml(packageName, package, null);
Dts.TaskResult = (int)ScriptResults.Success;
Voilà.