There are several reasons why DBAs will need to export/import SSIS packages for upgrades, development environments and backups. Many use SSMS to do imports and exports of the dtsx packages. It isgreat unless you need to export several or even all the packages. Microsoft supplies a command line tool dtutil.exe to import, export, delete, update and verify SSIS packages.
I wrote the script below to query msdb and script out the dtutil commands. I save the output to text or file. It can be executed windows batch file(.cmd or .bat). If your environment uses configuration files(.dtsconfig), make sure you grab a copy of those, too.
USE msdb
GO
SET NOCOUNT ON
GO
-- Scripts DTUTIL commands
-- Results to Text
-- Script out commands to execute on target server
-- Output to be used as a windows batch file
-- Tested: SQL 2008 R2 and SQL 2012
-- REMOVE " ---- " before saving as a .cmd or .bat file
-- JCD 01/29/2013
DECLARE @folderlocation VARCHAR(300)
-- Set location of folder to store
SET @folderlocation = 'S:\SSISPackages'
--------------------------
--- Do not alter below ---
--------------------------
-- Add Source Servername to folder structure (ex.C:\SSISExport\MyServer\)
SET @folderlocation = @folderlocation + '\'+CONVERT(VARCHAR(50),ServerProperty('SERVERNAME'))+'\'
--- Create Folder Structure
SELECT 'MKDIR "'+@folderlocation+'"'
UNION
SELECT 'MKDIR "'+ @folderlocation+RTRIM(foldername)+'"'
from [msdb].[dbo].[sysssispackagefolders]
-- Create DTUtil Commands
select 'DTUtil /SQL ' +
'"\' + CASE WHEN DATALENGTH(RTRIM(F.foldername)) > 0 THEN RTRIM(F.foldername)+'\' ELSE '' END +
P.name + '"' + ' /SourceS "'+CONVERT(VARCHAR(50),ServerProperty('SERVERNAME'))+'" /Encrypt FILE;"'+@folderlocation+
CASE WHEN DATALENGTH(RTRIM(F.foldername)) > 0 THEN RTRIM(F.foldername)+'\' ELSE '' END + P.name + '.dtsx";0 /Q'
from [msdb].[dbo].[sysssispackages] P inner join [msdb].[dbo].[sysssispackagefolders] F
on P.folderid = F.folderid
ORDER BY F.foldername
GO
