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.
First, I created a list of the sites I needed then a simple batch file containing the MSDeploy commands. Be careful of virtual directories. I had virtual directories that went to some large document repositories. The code I’m giving you only handles the root directory of the site and its physical subdirectories.
appcmd list vdir> c:/sites.text
With some editing of the file, I created an import format that I could use in Excel then used the concatenate excel function to build the msdeploy lines below.
MsDeploy is now a separate install. Once installed you will find it at c:\program files\iis\Microsoft Web Deploy V3. Run your commands from this directory.
It’s a great deal easier to directly transfer all the website files first. I personally use FileZilla between servers. Once done here’s the two commands I use to export a site then import it to a new site. The example site here is 8aSelect and I kept the archivedirectory in c:\resources
Command to export a site and not include the contents of the site itself
msdeploy -verb:sync -source:apphostconfig=”8aselect” -dest:archivedir=c:\resources\8aselect.zip,encryptPassword=MyPassKey -skip:objectName=dirPath,absolutePath=”d:\\websites\\8aselect”
Command to import a site and skip deleting existing files
msdeploy -verb:sync -source:archivedir=”c:\resources\8aselect.zip”,encryptPassword=MyPassKey -dest:appHostConfig=”8aselect” -skip:objectName=filepath,skipAction=delete