Bulk export of SSIS packages stored in the msdb library

http://blog.51cto.com/ultrasql/1924464

Use msdb goif object_id ('msdb.dbo.usp_ExportSSISPkgs') is not NULL DROP PROCEDURE dbo.usp_exportssispkgs; Goexec dbo.usp_exportssispkgs'E:\temp\ 'CREATE PROCEDURE dbo.usp_exportssispkgs @exportPath NVARCHAR ( -)='D:\temp\ 'as BEGIN DECLARE @pkgData XML, @pkgName NVARCHAR ( +), @pkgFolder NVARCHAR (4000), @cmd NVARCHAR (MAX); PRINT'--info:create temp tables.'; IF (object_id ('Tempdb.dbo.tbl_SSISPkgXML') is not NULL) BEGIN PRINT'--info:drop Existing temp table Tempdb.dbo.tbl_SSISPkgXML.';      DROP TABLE Tempdb.dbo.tbl_SSISPkgXML; END CREATE TABLE Tempdb.dbo.tbl_SSISPkgXML (ID INT IDENTITY (1,1), Pkgname NVARCHAR ( +) NULL, Pkgfolder NVARCHAR (MAX) NULL, PkgID VARCHAR ( +) Null,pkgdata XML NULL); PRINT'--info:insert package data into Tempdb.dbo.tbl_SSISPkgXML.'; IF (SELECT CHARINDEX ('SQL Server 2005', @ @VERSION)) >0--2005version BEGIN--use recursive CTE toGetFull path forSSIS packages on msdb SET @cmd='with Tbl_ssispkgfolder (FullPath, FolderID)as (SELECT CONVERT (NVARCHAR (MAX),"'\root"'), FolderID from Msdb.dbo.sysdtspackagefolders90 WHERE Parentfolderid are NULL UNION all SELECT CONVERT (N VARCHAR (MAX), T.fullpath+"'\"'+s.foldername), S.folderid from Msdb.dbo.sysdtspackagefolders90 s joins Tbl_ssispkgfolder T on S.parentfolderid =T.folderid) INSERT into Tempdb.dbo.tbl_SSISPkgXML (pkgname,pkgid,pkgfolder,pkgdata) SELECT p.name, P.I D, F.fullpath, cast (CAST (packagedata as varbinary (MAX) as XML) from Msdb.dbo.sysdtspackages90 p JOIN tbl_ssispkgfol Der F on P.folderid=f.folderid;'; EXEC (@cmd); END ELSE-- -or later version BEGIN SET @cmd='with Tbl_ssispkgfolder (FullPath, FolderID)as (SELECT CONVERT (NVARCHAR (MAX),"'\root"'), FolderID from Msdb.dbo.sysssispackagefolders WHERE Parentfolderid are NULL UNION all SELECT CONVERT (N VARCHAR (MAX), T.fullpath+"'\"'+s.foldername), S.folderid from msdb.dbo.sysssispackagefolders s joins Tbl_ssispkgfolder T on s.parentfolderid< /c1>=T.folderid) INSERT into Tempdb.dbo.tbl_SSISPkgXML (pkgname,pkgid,pkgfolder,pkgdata) SELECT p.name, P.I D, F.fullpath, cast (CAST (packagedata as varbinary (MAX) as XML) from Msdb.dbo.sysssispackages p JOIN tbl_ssispkgfold Er f on P.folderid=F.folderid WHERE ISNULL (p.description, "" ") Not like"'System Data Collector Package"'          ;'; EXEC (@cmd); END PRINT'--info:enable xp_cmdshell to allow access File System from SQL Engine'; EXEC sp_configure'Show advanced Options',1;      RECONFIGURE with OVERRIDE; EXEC sp_configure'xp_cmdshell',1;  RECONFIGURE with OVERRIDE; PRINT'--info:start Exporting ...'DECLARE cur_dtsxfile CURSOR for SELECT pkgname,pkgdata, pkgfolder from Tempdb.dbo.tbl_SSISPkgXML;      OPEN Cur_dtsxfile;      FETCH NEXT from Cur_dtsxfile to @pkgName, @pkgData, @pkgFolder; while (@ @FETCH_STATUS=0) BEGIN PRINT'--info:create Package folder under'[Email protected]+"'; SET @cmd= N'EXEC xp_cmdshell N"'mkdir "'+ @exportPath [Email protected]+'\"" ";        EXEC (@cmd); SET @cmd=n'bcp "Select Pkgdata from Tempdb.dbo.tbl_SSISPkgXML'+'WHERE pkgname=" '"[Email protected]+" '"and pkgfolder=" '"[Email protected]+" '""Queryout"'+ @exportPath + @pkgFolder +'\ ' [Email protected]+'. dtsx"-t-w-S"'[email protected] @SERVERNAME +' "'; SET @cmd = N'EXEC xp_cmdshell N" "[Email protected] +" '" ; PRINT'--info:export Package'+quotename (@pkgName) +' to'+ @exportPath [Email protected]+"';        EXEC (@cmd);      FETCH NEXT from Cur_dtsxfile to @pkgName, @pkgData, @pkgFolder;      END CLOSE Cur_dtsxfile;    Deallocate Cur_dtsxfile; END

Bulk export of SSIS packages stored in the msdb library