（5.3.2）数据库迁移——SSIS包批量导出

SSIS连接出错

　　

 

 原因 ：

   ssms 工具 不是 admin 权限 打开的 

 

SSIS包批量导出代码

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:\pag' 
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