I tried opening the designer for a package on an old SSIS dtproj from Windows Server 2012R2 on Windows Server 2019. I kept getting mysterious failures.  So I solved it by creating a new SSIS Integration Services project then adding each package individually.  Before I did this, I opened the code and changed the connection strings and used windows authentication and (local) instead of the server name.

I was wondering why, so I asked CHATGPT the following question:

In order to use SSIS packages on a Windows 19 server from windows server 2012 R2, why did I have to create a new project then Add Existing Packages. Why did the old solution fail every time I tried to open a package in the designer?

CHATGPT actually answered the question.

There could be several reasons why the old solution failed to open SSIS packages in the designer. One possibility is that the version of SQL Server Data Tools (SSDT) being used on the Windows Server 2012 R2 machine is not compatible with the version of SQL Server Integration Services (SSIS) used to create the SSIS packages.

In this case, creating a new project in SSDT and then adding the existing packages would allow the packages to be opened and edited in the newer version of SSDT.

Another possible reason could be that the old solution was corrupted or had missing dependencies that prevented it from opening the packages properly. Creating a new project would ensure that all the necessary dependencies are present and that the project is not corrupted.

It’s also worth noting that different versions of SQL Server may use different formats for SSIS packages, which could cause compatibility issues when opening older packages in a newer version of SSIS. Creating a new project and adding the existing packages ensures that the packages are converted to the correct format for the new version of SSIS.

Location of file on Windows Server 2012 c:\program files\Microsoft SQL Server\120\DTS\Binn

I listed the packages with this SQL Query then created a batch file with the commands below

select * from msdb.dbo.sysssispackages

Exporting SSIS Packages to File:

dtutil /SQL ExportPackage /COPY FILE;C:\Packages\ExportPackage.dtsx

Importing SSIS Package to SQL:

dtutil /file C:\Packages\ImportPackage.dtsx /copy sql;ImportPackage

I recommend opening the files in a text editor and making sure the connection strings are not pointing to an old server.

Also before importing the files, create a visual studio project. Import in all the packages. Go to the properties of the project, choose configuration, build and choose the version of sql server you’re using. Rebuild all the DTS packages.

You do have the option of creating a new visual studio project and importing in all the packages and performing an in place upgrade. However some of these were written in an older version and hard to upgrade. I just used the SQL Server versioning in VisualCron, our Job scheduler, and in some cases 32 bit mode.