Comment faire tourner SSIS dans SQL Server Agent


SSIS et SQL Server Agent sont deux produits, qui comme le dit la chanson des Beatles, vont très bien ensemble. 
Ou plutôt, devraient aller très bien ensemble.
Car voilà : lorsqu'on essaye de scheduler un package dtsx provenant de SSIS dans SQL Server Agent, pour - au hasard - le déclencher toutes les nuits, il est courant d'avoir des soucis, notamment quand le package SSIS se connecte à des bases de données ; SQL Server Agent sort une erreur liée au type de package SSIS, EncryptSensitiveData.

Mais reprenons calmement.

SSIS, comme on le sait, permet graphiquement de créer des transformations de données, mais bien plus encore, vu qu'il est possible d'intégrer des programmes C# dans des packages SSIS. L'utilisation courante est de se connecter à une base de données, de lire les Data d'une table et de les transformer pour les envoyer dans une autre.

Cela se traduit, dans SSIS, par la création d'un objet Connection SQL, qui se fait classiquement en spécifiant le serveur de données SLQ Server, le login et le mot de passe.

Ce mot de passe est enregistré, par défaut, dans le package SSIS, sous forme encryptée. Cela, parce que par défaut un package SSIS est en ProtectionLevel : EncryptSensitiveWithUserKey.
(Remarque : le ProtectionLevel est une propriété du package, disponible quand on clique en Control Flow sur le fond du package, qui détermine comment sont sauvegardées les données "sensibles" : mots de passe, etc...)

Et c'est là que le bat blesse.

Car, on peut créer un job sous SQL Server Agent et spécifier un package SSIS, MAIS : SQL Server Agent ne supporte pas les packages en ProtectionLevel : EncryptSensitiveWithUserKey.


Qu'à cela ne tienne me direz vous, il suffit de retourner dans SSIS et de changer la propriété ProtectionLevel à DontSaveSensitive : ouf, ça y est, SQL Server Agent supporte ce type de package, il le démarre.
Mais dans ce cas, le mot de passe de la connexion SQL n'est plus sauvegardé, et le package ne peut donc plus se connecter à la base.
D'où plantage.

Un truc de fou, quoi.

Comment faire pour s'en sortir ? 
Par la ruse, vous allez voir ;)


En fait, nous allons conserver le paramétrage DontSaveSensitive, et nous allons mettre la connexion à la base de données dans un fichier de configuration paramétrable. Ce qui permettra accessoirement de changer plus facilement les paramétres de connexion.

1)   Dans SSIS, changer le niveau de protection du package dans les propriétés :
  • Cliquez sur le fond du package, pour ne pas sélectionner une Task SSIS (sinon, c'est la task qui aura cette propriété).
  • Dans la propriété  ProtectionLevel, choisissez DontSaveSensitive

2)   Créer les variables ConnectionString nécessaires :
  • Cliquez sur le fond du package, pour ne pas sélectionner une Task SSIS (sinon,  la variable créée ne sera valable qu'au sein de la Task ).
  • Dans SSIS, choisissez menu SSIS / Variables
  • Dans la fenêtre Variables, ajoutez une nouvelle variable ; on l'appellera MyConnectionString, et on prendra String pour type.



3)   Créer le fichier de configuration pour le package :
  • Dans le menu SSIS / Package Configurations, cliquez sur "Enable package configurations" 
  • Puis Add...
  • Choisissez XML Configuration File
  • Nommez le en choisissant le nom du package + l'extension ".dtsConfig", et mettez le dans le même répertoire que le package lui-même (plus facile à retrouver), puis Next >
  • Une liste de variables va apparaître : choisissez la variable MyConnectionString créée précédemment, déroulez, déroulez Properties et cochez Value
  • Puis OK.

SSIS va créer un fichier de configuration comportant une clé XML reprenant la variable choisie. Sa valeur est vide pour l'instant. 


4)   Modifier les propriétés de la connexion :
Il faut maintenant lier la variable SSIS créée et l'objet Connection permettant la connexion à SQL Server.
  • Cliquer sur l'objet Connection SQL que vous aviez créé avant de commencer, dans Connection Managers en bas de l'écran
  • Dans Properties, cliquez sur Expressions : puis cliquez sur le bouton avec les ...
  • Dans la liste déroulante, choisissez ConnectionString
  • Indiquez la valeur à mettre pour cette expression : cliquer sur ... , puis dans la fenêtre qui s'ouvre, déplier Variables en haut à gauche, repérez User::MyConnectionString, et glissez la dans la fenêtre du bas.

5)   Renseigner la connextionString dans le XML de configuration :
  • Editez le fichier de configuration créé auparavant, 
  • Saisissez une chaîne de connexion avec une syntaxe correcte.

Il existe des sites web permettant de générer des chaînes de connexion ; en voici 2 comme exemple :
ADO.NET : Data Source=monServeur;User ID=monUser;Initial Catalog=maBase;Provider=SQLNCLI10.1;Persist Security Info=True;password=MonMotDePasse;
OleDB : Data Source=monServeur;User ID=monUser;Initial Catalog=maBase;Persist Security Info=True;password=MonMotDePasse;

Une fois toutes ces opérations réalisées, le fichier de configuration est relié à l'objet Connection.
Ainsi, quand vous lancerez le package dans SSIS, il lira le fichier de configuration, remplira la variable ConnectionString et placera sa Value dans l'objet Connection, ce qui aura pour effet d'ouvrir la connexion à SQL Server.

Et maintenant, vous pouvez enfin utiliser votre package dans SQL Server Agent : il est bien en "DontSaveSensitive", et il a les informations pour se connecter.
Previous
Next Post »