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()='D:\temp\'

AS

BEGIN

  DECLARE @pkgData XML, @pkgName NVARCHAR(),@pkgFolder NVARCHAR(), @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(,), PkgName NVARCHAR() NULL, PkgFolder NVARCHAR(MAX) NULL,

           PkgID VARCHAR() NULL,PkgData XML NULL);    

  PRINT '--Info: Insert Package data into tempdb.dbo.tbl_SSISPkgXML.';

  IF (SELECT CHARINDEX('SQL Server 2005', @@VERSION))>

  -- 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

  -- 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',;

  RECONFIGURE WITH OVERRIDE;

  EXEC sp_configure 'xp_cmdshell',;

  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=)

  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