April 30, 2020 at 10:15 pm
We have a project with 650+ packages for datafeeds and order files. Most of this work was done on two systems and for a long time the encryption method was EncryptSensitive with UserKey. Then when transferring back and forth between members in sourcecode we would always have a problem with our project file and would need to merge.
We just moved from Azure TFVC to Azure Git and we split this project away from some other database projects so it could stand alone. We also decided to change the project to EncryptSensitive with Password. Now we have all kinds of problems.
So first, we changed the project settings to use this method and set the new password. Then we updated every single package with the same information. After that I was able to build. However, after I pushed the changes and my co-worker pulled them in (project and ALL packages) when he tried to build he would get...
"Project consistency check failed. The following inconsistencies were detected..." then it lists all 650+ packages. After some reading we thought it was possible that this was because they needed to be built on his machine? I don't know. But we basically went through again, 650+ packages, changed all the settings. It built fine. He pushed, I pulled. As soon as I built I got the error.
So we can get it to work on one machine but every time we make a change and do a push/pull the packages need to be set again.
Also, all this work is occurring in Visual Studio. But I tried initially to change the levels using dtutil. I ran this command...
for %f in (*.dtsx) do dtutil.exe /file %f /encrypt file;%f;2;[our password] /quiet
…. in the folder that the packages all reside.
It ran without any errors, it said for every file "The operation completed successfully." However, when I open up the project it still will not build, the inconsistencies error. If I go through and do them all by hand it will then build and work.
We're running SQL Server 2016 and Visual Studio Community 2019, using Azure Git for source control.
Does anyone have any idea what we're doing wrong. This is driving us crazy. Thanks.
April 30, 2020 at 10:22 pm
What are your reasons for not using Don't Save Sensitive & supplying sensitive data via parameters and SSISDB environments?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 1, 2020 at 12:41 pm
I am going to test this now. When this was originally set up I believe encrypting the data was believed to be the best practice. But all of our passwords and connection strings are provided via parameters and environments. I believe that's why these packages may have been running successfully even though we were using 2 different User Keys.
I'm going to try that. It still doesn't explain why dutil is not working properly. I'll try it with No Encryption and see what happens.
May 1, 2020 at 12:49 pm
I am going to test this now. When this was originally set up I believe encrypting the data was believed to be the best practice. But all of our passwords and connection strings are provided via parameters and environments. I believe that's why these packages may have been running successfully even though we were using 2 different User Keys.
I'm going to try that. It still doesn't explain why dutil is not working properly. I'll try it with No Encryption and see what happens.
Sorry, can't help with the original problem. Everywhere I have worked, we have used Don't Save Sensitive, because the other methods always seemed to create annoying issues.
Please post back with your findings.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 1, 2020 at 1:42 pm
Ugh, this is getting weird.
So I ran this:
for %f IN (*.dtsx) DO dtutil.exe /file %f /encrypt file;%f;0 /quiet
It appears to have worked. All of the projects now have the ProtectionLevel set to DontSaveSensitive. So that does work.
Then, I set the project property to ProtectionLevel = DontSaveSensitive
Then I built the project (I also tried rebuild). I am getting the same error. "Project consistency check failed. The following inconsistencies were detected: …" followed by a list of all of my packages.
Now, just like before, in order to get it to build I have to open each package individually, click the ProectionLevel drop down and choose another choice then re-click it and choose DontSaveSensitive. Then, after that if I build the item drops out of the error list. So I'm going to have to do this for all of the items (even though they already say DontSaveSensitive). But I suspect after I do all this and check it all in, when my co-worker pulls my changes it's going to happen again.
This is so frustrating. I don't know what else to do but try. I'll update in a couple hours when this is done.
If anyone has any insight please let me know. I have attached images of the settings just so you can see, maybe I'm missing something.
May 1, 2020 at 1:50 pm
Once all of the projects and packages are set to Don't Save Sensitive and checked in (don't forget to check in the project file as well as the packages), I'd be surprised if your co-worker had problems ... looking forward to the next update.
I've never tried using dtutil to change package protection levels, so can't comment on that.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 1, 2020 at 9:49 pm
As a thought, when you ran dtutil.exe, is %f the FULL path to the file or the relative path? May want to remove the "/quiet" option and just see what dtutil says for at least one of the dtsx's.
my GUESS (as I haven't used dtutil before either) is that it is erroring on the files so since it is told be quiet, it actually did nothing. The documentatio for the /encrypt option says FILE parameter to encrypt needs to be "FILE - Path is the fully-qualified path and file name for the package."
That being said, it looks like "quiet" just stops prompts for copy, move or sign, so not positive that removing that will help with anything.
Still thinking that it is that FILE needs to be fully qualified path and file name, not just filename though that is giving you grief with that command.
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.
May 4, 2020 at 8:41 pm
I tried without /quiet first. It was just a confirmation to overwrite. It did not throw any errors. Fully qualified path also did not work. Frustrating.
Anyway, I just stripped out the encryption. I changed the project and the packages to DontSaveSensitive. I still could not build, I had to open every package and modify it and build it. It was already set to DontSaveSensitive because I tried dtutil first. In order to get it to work I had to open the package, change to a different choice in the ProtectionLevel DDL, then set it BACK to DontSaveSensitive and then Build. Once I did that it dropped out of the error message.
The good thing is that after PUSH'ing all my changes when my co-worker pulled it down he did not have to rebuild all the packages, it just worked (unlike password encryption). So I'm passed this but never did figure out why it isn't working properly.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply