[SSIS] Connexions dynamiques (SQL et Analysis Services)

Un article technique, vu que ça fait un petit bout de temps que j'en ai pas écrit.

Le contexte :on a tous déjà utilisé SSIS pour faire des tâches d'administration de nos bases de données (SQL et/ou SSAS). Pour cela on configure nos connexions de manière dynamique. Et autant, ça marche plutôt bien dans le cas d'une base SQL, autant SSIS ne réagit pas de la même manière pour du SSAS.

Note : Ce qui est intéressant ici est le multi serveur. Si toutes vos bases sont sur le même serveur, il n'y a aucun problème.

Bases SQL :

Ici on va créer un package qui fait un back-up de bases de données dont le nom et la chaîne de connexion ont été renseignés dans une table (et accessoirement le répertoire de backup parce que normalement ils sont propres au serveur).

Le package sera constitué d'un SQL Task qui lit le contenu de cette table pour le mettre dans une variable de type objet, et d'un foreach qui bouclera sur ces différents noms et chaînes de connexions que l'on stocke dans des variables textes.

On utilise une expression pour définir le ConnectionString de notre connexion qui se base sur la 2ème variable.

On crée également une variable contenant la requête SQL à exécuter pour faire le back-up de nos bases.

"BACKUP DATABASE ["+ @[User::DatabaseName] +"] TO  DISK = N'"+ @[User::BackupDirectory] +"\\"+ @[User::DatabaseName] +".bak' WITH NOFORMAT, NOINIT,  NAME = N'"+ @[User::DatabaseName] +"-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10GO"

On exécute, on vérifie dans nos répertoires de back-up SQL, tout s'est bien passé.

Ça veut donc bien dire que SSIS réussit à rafraîchir la connexion pendant l'exécution du package, lorsqu'il s'agit d'un type Ole DB.


Bases Analysis Services :

C'est ici que ça se complique, ou plutôt que SSIS n'est pas aussi permissif avec les connexions aux bases SSAS.

On va créer un package qui processe des cubes dont le nom et la chaîne de connexion sont renseignés dans une table.

De la même manière que pour les bases SQL, on utilise un SQL Task pour lire la table que l'on ramène dans une variable objet. Et ensuite un foreach où on boucle sur les différentes valeurs que l'on stocke dans 2 variables textes (DatabaseName et ConnectionString)
On utilise enfin une expression sur la ConnectionString de la connexion SSAS en assignant la valeur de la 2ème variable (même principe que pour les bases SQL aussi)

Et ici commence les problèmes :

En exécutant le package, on obtient une erreur :

En fait, la connexion n'est tout simplement pas évaluée : Il n'y avait pas de valeur initiale dans ma variable SSASConnectionString, la chaîne est donc incorrect.

Si vous initialisez la variable avec une chaîne de connexion valide, le package va rester sur cette valeur, et donc planter sur le process devant s'exécuter sur un autre serveur.

SSIS est incapable de rafraichir dynamiquement une connexion SSAS.

J'insiste sur la note donnée en début d'article : s'il y a plusieurs bases sur un même serveur et que ce serveur sert à l'initialisation de la variable, vous n'aurez pas de soucis. C'est bien le fait de devoir changer de serveur à la volée qui pose problème.

Et une petite astuce en conclusion pour contourner le problème :

Faites des tâches de script en AMO, vous pourrez alors reconstruire la connexion dans votre script à partir de votre variable.