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(2000)=‘D:\temp\‘    
AS    
BEGIN

  DECLARE @pkgData XML, @pkgName NVARCHAR(1000),@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(1000) NULL, PkgFolder NVARCHAR(MAX) NULL,    
           PkgID VARCHAR(40) 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 JOIN 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 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 INTO @pkgName, @pkgData, @pkgFolder;    
  WHILE (@@FETCH_STATUS=0)    
  BEGIN    
    PRINT ‘--Info: Create Package folder under ‘+@exportPath+ ‘‘;    
    SET @cmd= N‘EXEC xp_cmdshell N‘‘mkdir "‘ + @exportPath +@pkgFolder+ ‘\"‘‘‘;    
    EXEC(@cmd);    
    SET @cmd=N‘bcp "SELECT PkgData FROM tempdb.dbo.tbl_SSISPkgXML ‘    
   + ‘WHERE PkgName=  ‘‘‘‘‘+@pkgName+‘‘‘‘‘ AND PkgFolder= ‘‘‘‘‘+@pkgFolder+‘‘‘‘‘" queryout "‘    
   + @exportPath+ @pkgFolder+ ‘\‘+@pkgName+‘.dtsx" -T -w -S"‘+@@SERVERNAME+‘"‘ ;    
    SET @cmd = N‘EXEC xp_cmdshell N‘‘‘+@cmd +‘‘‘‘ ;    
    PRINT ‘--Info: Export package ‘+QUOTENAME(@pkgName)+‘ to ‘ + @exportPath +@pkgFolder+‘‘;    
    EXEC(@cmd);    
    FETCH NEXT FROM cur_DtsxFile INTO @pkgName,@pkgData,@pkgFolder;    
  END    
  CLOSE cur_DtsxFile;    
  DEALLOCATE cur_DtsxFile;    

END

本文出自 “SQL Server Deep Dive” 博客，请务必保留此出处http://ultrasql.blog.51cto.com/9591438/1924464

批量导出存储在msdb库的SSIS包