February 13, 2020 at 6:10 pm
We're testing an upgrade from SSIS 2016 (developed in VS2015) to SSIS 2019 (developed in VS2017). I've got the latest VS2017 versions installed:
I upgraded the packages by changing the TargetServerVersion to SQL Server 2019 (was previous SQL Server 2016). After doing this, it takes 15-20 minutes, but then all packages are upgraded. When diffing the XML, I can see the updated version:
I can also see that the version is updated in my Execute SQL Commands:
However, every single connection string in the solution appears to be corrupted:
connectionManagerRefId="Project.ConnectionManagers[DBName]"
connectionManagerRefId="{3947F6AA-B30C-4A31-9922-B14F26BB5555}:invalid"
These are all project level connections. If I open the control/data flow object with the corrupted connection string, close it, and save it, then it self-corrects. The problem is that we have over 500 packages, and we heavily use cache connection managers for data warehouse lookup tasks, which are all also corrupted. Bottom line is that it's not realistic to manually "touch" every corrupted object to fix it.
Has anyone else come across this issue and found a better resolution? Am I doing something wrong when upgrading? I also tried Visual Studio 2019 with SSIS V3.4 but got the same results.
February 14, 2020 at 7:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 9, 2020 at 2:50 pm
Did anyone ever find a solution for this?
I just changed the TargetServerVersion from SQL Server 2016 to SQL Server 2019 (using the SQL Server Integration Services Extension v3.10 in Visual Studio 2019) and it still messes up the connectionManagerID and connectionManagerRefId.
Luckily I'm using BIML so I could regenerate the packages, but the upgrade should just work, right?
November 10, 2020 at 2:58 am
Unfortunately, no. We scrapped the upgrade to 2019 and instead only upgraded to 2017. If you end up figuring out a solution (other than regenerating the packages or manually fixing by opening each package), please post it here!
November 10, 2020 at 2:52 pm
In our case it was simply a missing SQL Server 2019 version of a custom connection we have in our SSIS project. After installing the 2019 version everything worked fine.
I'm seeing that "connectionManagerID= {}:invalid" as well, but it doesn't seem to make any difference. The package runs fine anyway.
I'm using v3.10 of the SSIS extension and Visual Studio 2019 Version 16.7.7 so you might give that a try.
May 5, 2022 at 10:36 pm
I just upgraded from 2016 to 2019 (VS2015 to VS2019) using the method of changing the targetserverversion to SqlServer 2019....and experienced no corruption of connections. We use only shared project-level connections. We have number of add-on adapters including bixpress, bimlexpress, task factory, bixpress, kingswaysoft and everything seems to have converted. All adapters were already updated to 2019 supported versions prior to upgrade. Task factory, bixpress and bimlexpress requires vsix installs to 2019. I suspect the bugs reported have been fixed since it seems to no longer have the issue reported here.
Issue: Opening each package the first time there were a LOT of errors and it took about 20 minutes to open a simple upgraded package. After opening and saving then the next time it opened fast with no errors. It appears every package will have to be opened and saved one time. Curious whether there is an automated method of doing this rather than attempting to open and save all the packages (we have thousands).
Running VS community edition Version 16.11.13
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply