lundi 18 avril 2011

Exporter les packages SSIS stockés sur MSDB vers un répertoire physique

Le fameux coup du serveur de production complètement désynchronisé avec votre serveur de développement.

Et vous voulez redescendre tous les packages SSIS sur votre environnement de développement.

Voilà donc une petite procédure toute simple, basée sur DTUTIL et qui fait appel à la récursivité pour construire l'arborescence des répertoires de la base MSDB vers notre répertoire de destination et des curseurs (Il est sûrement possible de s'en sortir avec du "Select @strsql =  @strsql+...)

On pourra également définir le dossier de destination de l'export comme paramètre d'entrée de la procédure stockée.

A noter que les packages en niveau de protection "Server Storage" ne peuvent pas être copiés (Il est sûrement possible de changer le niveau de protection avec la commande ENCRYPT de DTUTIL avant l'export, à vous de jouer...)

La commande xp_cmdshell doit être active au niveau du serveur (Un petit coup de sp_configure xp_cmdshell, 1 GO RECONFIGURE si besoin en ayant préalablement activer les propriétés avancées)



Create proc [dbo].[PS_DEPLOY_ALL_PACKAGES_FROM_DTS_TO_FILE]
as
Declare @srcpackagename as varchar(max)
Declare @foldertocreate as varchar(max)
Declare @strsql as nvarchar(max)
Declare @folderdest as varchar(max)
set @strsql=''
set @folderdest='f:\'
EXEC [master].[sys].[xp_cmdshell] 'rmdir f:\msdb /s /q'
;WITH foldertable
(folder,folderid, nblevel)
as
(select cast(foldername as varchar(max)) as folder,folderid,as nblevel
From [msdb].[dbo].[sysssispackagefolders]
Where parentfolderid='00000000-0000-0000-0000-000000000000'
union all
Select cast(b.folder+'\'+a.foldername as varchar(max)) as folder,a.folderid as folderid,nblevel+1
From [msdb].[dbo].[sysssispackagefolders] a
inner join foldertable b
on a.parentfolderid=b.folderid)
select * into #TMP_PACKAGE from foldertable;
DECLARE srcpackage Cursor
FOR
SELECT Distinct 'MSDB'+isnull('\'+F.folder+'\','\')+L.[name],'MSDB'+isnull('\'+F.folder+'\','\')
FROM [msdb].[dbo].[sysssispackages] L
left join #TMP_PACKAGE F
ON L.[folderid]=F.[folderid]
WHERE L.[packageformat]=0;
OPEN srcpackage
FETCH NEXT FROM srcpackage INTO @srcpackagename,@foldertocreate
WHILE @@FETCH_STATUS = 0
BEGIN
set @strsql=@strsql+';EXEC [master].[sys].[xp_cmdshell] ''md "'+@folderdest+@foldertocreate+'"'',NO_OUTPUT;EXEC [master].[sys].[xp_cmdshell] ''dtutil /DTS "'+@srcpackagename +'" /COPY FILE;"'+@folderdest+@srcpackagename+'.dtsx" /Q >>'+@folderdest+'MSDB\log.txt'',NO_OUTPUT'
--exec sp_executesql @strsql
FETCH NEXT FROM srcpackage INTO @srcpackagename,@foldertocreate
END
exec sp_executesql @strsql
CLOSE srcpackage
DEALLOCATE srcpackage
Drop table #TMP_PACKAGE