SSIS Package batch export and import

1.

You can create batch export command by following sql.

select 'dtutil /SQL "' +[p].[name] + '" /ENCRYPT FILE;"F:\SSISPackages\'+[p].[name]+'.dtsx;1"'
from msdb.dbo.sysdtspackages90 [p]

Output command will be like this.

   dtutil /SQL "SSIS_Package_Name" /ENCRYPT FILE;"F:\SSISPackages\SSIS_Package_Name.dtsx;1"

Here 1 means, copy all the sensitive data with user key.
Here we are exporting SSIS packages from SQL server to F:\SSISPackages directory.

Run these batch commands in Souce SSIS server's command prompt.

Copy all the .dtsx file from source to destination server.

2.
Batch SSIS packages import commands can be generated by following sql

select 'dtutil /FILE "C:\Users\s5142805\Downloads\SSIS pkg\' +[p].[name] + '.dtsx" /COPY SQL;"/'+[p].[name]+'"'
from msdb.dbo.sysdtspackages90 [p]

Output will be like this.

dtutil /FILE "C:\Users\s5142805\Downloads\SSIS pkg\ABMCashOut - Import v2.dtsx" /COPY SQL;"/ABMCashOut"

Here we are importing SSIS packages from C:\Users\s5142805\Downloads\SSIS pkg\ to SQL server directory.

Run these batch commands in Destination SSIS server's command prompt.