-> In one of my project I had to migrate SQL Server Integration Services packages from SQL Server 2008 R2 to SQL Server 2016.
-> I planned to export the existing SQL Server Integration Packages from SQL Server 2008 R2 as DTSX files and then import it into SSDT.
-> But I had close to 250 + Packages and exporting each one will take more time.
-> I used below script and executed in msdb database.
set nocount on select foldername as FolderName, [name] as PackageName into #PackageNames from msdb.dbo.sysssispackages pkg join msdb.dbo.sysssispackagefolders fld on pkg.folderid = fld.folderid order by FolderName, PackageName select 'dtutil /SQL ' + case when len(FolderName) = 0 then '"' else '"'+FolderName + '\' end + PackageName + '"'+ + ' /COPY FILE;'+'"'+'C:\temp\SSIS_Packages\' + case when len(FolderName) = 0 then '' else FolderName + '\' end + PackageName + '.dtsx' + '"' + ' /QUIET' --suppresses prompt to delete if an older file with same name exists from #PackageNames drop table #PackageNames
-> Copy the output and execute it in command prompt. You can also save it as a batch file and execute it. But be sure to check the output to make sure there are no errors.
-> I received below message for some packages,
The package SSISpackagename can not be saved to the file system since its protection level is server storage. Use the encrypt action to change the protection level.
-> I had to manually export it and select the protection level as appropriate. In my case I just had 3 packages, so manual method for those packages were best fit for me.
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.