ssis mysql ado.net 迁移_（5.3.2）数据库迁移——SSIS包批量导出

usemsdbgo

IF OBJECT_ID('msdb.dbo.usp_ExportSSISPkgs') IS NOT NULL

DROP PROCEDUREdbo.usp_ExportSSISPkgs;go

CREATE PROCEDUREdbo.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 TABLEtempdb.dbo.tbl_SSISPkgXML;END

CREATE TABLE tempdb.dbo.tbl_SSISPkgXML(ID INT IDENTITY(1,1), PkgName NVARCHAR(1000) NULL, PkgFolder NVARCHAR(MAX) NULL,

PkgIDVARCHAR(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 WITHOVERRIDE;EXEC sp_configure 'xp_cmdshell',1;RECONFIGURE WITHOVERRIDE;PRINT '--Info: Start exporting...'

DECLARE cur_DtsxFile CURSOR FOR SELECT PkgName,PkgData, PkgFolder FROMtempdb.dbo.tbl_SSISPkgXML;OPENcur_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

CLOSEcur_DtsxFile;DEALLOCATEcur_DtsxFile;END