I encountered a scenario where a customer requested to move their existing SSIS packages from a 2008 R2 server to a 2016 SQL Server. This is not a typical request as it involves many considerations before migrating a package to a higher version. In this post, I will list the steps and considerations that I took before tackling this task of SSIS package migration.
Steps for migration of a package
Export the current packages to a folder: Identify how many packages are running on the source server. If there are only a few packages, such as 2-5, export them manually. When I encountered 10+ or even 100+ packages, I used the below script to list out all the packages and create an output that I ran to move all the packages to the specified folder, D:\WordsonTech.
USE MSDB
GO
SELECT 'EXEC XP_CMDSHELL ''DTUTIL /SQL ' + NAME +' /COPY FILE;D:\WordsonTech\' + NAME+'.DTSX' + ' /QUIET''' AS PackageCopyScript FROM SYSSSISPACKAGES
Before upgrade Check the dependencies that a package has:
- If package connects to multiple databases on different servers. Bring these databases to the stage server to remove database-related dependency errors.
- Manually validate the package’s connections to network paths and resolve any connectivity issues by following up with the concerned team.
- We address dependencies on third-party software and data flow components.
- We review each package’s event handling mechanisms.
- We validate the connectivity of various FTP and SMTP servers in each package and fix credentials after ensuring connectivity.
- We address dependencies on drivers and providers deployed according to new versions.
- We review and align each package’s configurations for the new system.
- We understand the use of various static and dynamic variables in packages.
- Ensure that production databases are not affected during the testing and validation phase by checking the code of stored procedures.
Upgrade Package versions: Open the .dtsx package file in a text editor, such as Notepad or Notepad++. Then, I replaced all instances of 10.0.0.0 (2008) with 13.0.0.0 (2016) and saved the modified file.
Update the package: Open the solution with BIDS (2008) or SQL Server Data Tools, which automatically converts the solution and package. BIDS is no longer integrated with SQL Server. Use either SSDT or add the SSIS component in Visual Studio to complete this task. Use this link to add SSDT to visual studio.
Deploy Package: Once I upgraded and updated the package, I deployed it to the new server, either in the SSIS catalog or to the msdb folder structure as per the customer’s requirement.
I hope that these steps will help you in SSIS Package Migration If you think any other steps can be added to smooth the process, please let me know.