Bulk export of SSIS packages stored in the msdb library

Use msdb
Go

IF object_id (' msdb.dbo.usp_ExportSSISPkgs ') is not NULL
DROP PROCEDURE dbo.usp_exportssispkgs;
Go

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 (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
--2005 version
BEGIN
--use recursive CTE to get full path for SSIS packages on msdb
SET @cmd = ' with Tbl_ssispkgfolder (FullPath, FolderID)
As
(SELECT CONVERT (NVARCHAR (MAX), ' \root '), FolderID from MSDB.DBO.SYSDTSPACKAGEFOLDERS90
WHERE Parentfolderid is NULL
UNION All
SELECT CONVERT (NVARCHAR (MAX), t.fullpath+ ' \ ' +s.foldername), S.folderid
From MSDB.DBO.SYSDTSPACKAGEFOLDERS90 S
JOIN Tbl_ssispkgfolder T on S.parentfolderid=t.folderid)
INSERT into Tempdb.dbo.tbl_SSISPkgXML (pkgname,pkgid,pkgfolder,pkgdata)
SELECT p.name, P.id, F.fullpath, cast (CAST (packagedata as varbinary (MAX)) as XML)
From Msdb.dbo.sysdtspackages90 P joins Tbl_ssispkgfolder F on P.folderid=f.folderid; ';
EXEC (@cmd);
END
ELSE
--2008 or later version
BEGIN
SET @cmd = ' with Tbl_ssispkgfolder (FullPath, FolderID)
As
(SELECT CONVERT (NVARCHAR (MAX), ' \root '), FolderID from Msdb.dbo.sysssispackagefolders
WHERE Parentfolderid is NULL
UNION All
SELECT CONVERT (NVARCHAR (MAX), t.fullpath+ ' \ ' +s.foldername), S.folderid
From Msdb.dbo.sysssispackagefolders S
JOIN Tbl_ssispkgfolder T on S.parentfolderid=t.folderid)
INSERT into Tempdb.dbo.tbl_SSISPkgXML (pkgname,pkgid,pkgfolder,pkgdata)
SELECT p.name, P.id, F.fullpath, cast (CAST (packagedata as varbinary (MAX)) as XML)
From Msdb.dbo.sysssispackages P JOIN tbl_ssispkgfolder F on P.folderid=f.folderid
WHERE ISNULL (P.description, "" ") not a 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

This article is from the SQL Server deep Dive blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1924464

Bulk export of SSIS packages stored in the msdb library