Lorsque l’on change le nom d’une instance SQL Server celui-ci ne se propage dans les connexions des plans de maintenance. Pour rappel, un plan de maintenance n’est ni plus ni moins qu’un package SSIS lié à un job SQL Server. C’est donc ce package SSIS qui contient la définition des sources de données. Cependant lorsqu’on tente de modifier la source de données par défaut associée au plan de maintenance on s’aperçoit vite qu’il est impossible de la modifier. On ne peut qu’en ajouter et modifier en conséquence chaque tâche qui compose le plan avec la nouvelle connexion. Autant dire que l’opération devient rapidement fastidieuse. Alors comment changer directement la source de données par défaut ?

Comme dit un peu plus haut, un plan de maintenance n’est ni plus ni moins qu’un package SSIS stocké dans la base de données msdb. On peut retrouver notre package dans la table msdb.dbo.sysssispackages. Sur mon serveur voici ce que j’obtiens :

select
id,
name,
description,
packagedata
from msdb.dbo.sysssispackages
where name = ‘MaintenancePlan';

qui donne :

La colonne qui nous intéresse ici est packagedata. Cette colonne au format image contient le contenu du package SSIS relatif à mon plan de maintenance. Hors il est également possible de lire la définition d’un package au format XML. Il suffit donc de convertir la colonne packagedata au format XML de la façon suivante :

select
id,
name,
description,
packagedata,
cast(cast(packagedata as varbinary(max)) as xml)
from msdb.dbo.sysssispackages
where name = ‘MaintenancePlan';

qui donne

La portion du document XML qui nous intéresse est la suivante :

L’élément DTS:Property avec l’attribut DTS:Name avant la valeur ConnectionString contient la chaîne de connexion qui nous intéresse. On peut donc facilement mettre à jour ce sous ensemble en convertissant la colonne packagedata au format chaîne de caractères puis en utilisant les fonctions de traitement de texte de la façon suivante :

DECLARE @old_server_name SYSNAME = ‘OldServerSQL';
DECLARE @new_server_name SYSNAME = @@SERVERNAME;
DECLARE @id_package UNIQUEIDENTIFIER;
DECLARE @package_name SYSNAME;
DECLARE @package_data_new VARCHAR(MAX);

DECLARE C CURSOR FOR
SELECT 
id,
name
FROM msdb.dbo.sysssispackages
WHERE CAST(CAST(packagedata AS VARBINARY(max)) AS VARCHAR(MAX)) LIKE ‘%<DTS:Property DTS:Name= »ConnectionString »>server= »’ + @old_server_name +  »';%';
OPEN C;
FETCH NEXT FROM C INTO @id_package, @package_name;
WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @package_data_new = REPLACE(CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)), ‘server= »’ + @old_server_name +  » », ‘server= »’ + @new_server_name +  » »)
  FROM msdb.dbo.sysssispackages;
  PRINT ‘Replace connection string for maintenance plan : ‘ + @package_name;
  –SELECT CAST(@package_data_new AS XML);
  UPDATE msdb.dbo.sysssispackages
  SET packagedata = CAST(@package_data_new AS VARBINARY(MAX))
  WHERE id = @id_package;
  FETCH NEXT FROM C INTO @id_package, @package_name;
END
CLOSE C;
DEALLOCATE C;

Bon changement de connexion !!

David BARBARIN (Mikedavem)
MVP SQL Server