批量导出存储在msdb库的SSIS包
时间：2021-07-01 10:21:17
帮助过：16人阅读
‘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
批量导出存储在msdb库的SSIS包
标签：proc figure ssis cte arch close from isp exist
