批量导出存储在msdb库的SSIS包
http://blog.51cto.com/ultrasql/1924464
use msdb go IF OBJECT_ID('msdb.dbo.usp_ExportSSISPkgs') IS NOT NULL DROP PROCEDURE dbo.usp_ExportSSISPkgs; go EXEC dbo.usp_ExportSSISPkgs 'E:\temp\' 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