September 13, 2023 at 1:25 pm
Good morning experts,
Over the past few releases, my team and I have been running into this issue where when we deploy an SSIS Project from one SQL Server to another we get this error on the "Changing Protection Level part:
Value does not fall within the expected range. (Microsoft.SqlServer.ManagedDTS)
We have about 15 projects and there are 2 that do not work. From googling, we found that it could be related to protection level or the target server in the project properties doesn't match but all the projects are identical and they do match. We are then forced to deploy from Visual Studio directly.
Our normal process is to right click the projects folder within the Integration Services Catalog and choose deploy and follow the onscreen instructions. This just recently started happening with these 2 projects but has always worked in the past. We don't have anything that we can think caused the issue but would love to hear if anyone else has had this issue or things to check.
This is currently SQL Server 2019 to SQL Server 2019 also.
Thank you,
Chris
September 14, 2023 at 2:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 14, 2023 at 6:55 pm
First step is to review the error message. Without seeing the EXACT error message (and any associated errors) it is hard to say what the problem is. If I had to guess, I would say that some private/protected fields were set up by the user who did the initial import and were not part of the export.
What you probably could do is re-create the ISPAC file on the new system using a similar method as you described except instead of picking "deploy", you'd pick "import packages". Not 100% sure that is the correct syntax, but there are 2 options at the top of the right-click menu. One is deploy and expects an ispac file, and the other is import (or similar) and expects a dtsx file.
When deploying an SSIS package, I USUALLY use a dtsx file and generate the ISPAC from SSMS rather than trying to do it in visual studio as I have at times accidentally generated the ispac for the wrong SQL version when I use visual studio. Using SSMS, I can make sure my SSMS version matches my SQL version and things mesh up nicely.
As a second thought - are you using the same SSMS version as your SQL Server version? That is, if you are using SQL 2019, is your SSMS version 15.x? I've had issues before (frequently) when my SSMS version doesn't match the SQL version while deploying an SSIS package. It is uncommon to have issues (for me) with an ispac, but frequent with DTSX's.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply