September 24, 2019 at 6:57 pm
All,
I've got an SSIS solution using project deployment that contains roughly 25 individual dtsx packages. (SSIS 2017) Our Dev, QA, and Prod servers are on different AD Domains. When I want to "get latest" from Prod with my solution and bring it back down to Dev, I thought I could just go into the SSISDB, right-click and do "Export." It gives me an .ispac which I can open in VS 2017 on my local machine. However, whenever I do it, all of the packages and the overall solution are defaulted to "EncryptwithUserKey." I have to go back through & change the setting on each of the 25 packages to "DontSaveSensitive" and then to the overall solution to "DontSaveSensitive" and re-build. Is there an easier way? I'm handling the "safety" of any connection strings/sensitive info outside the SSIS process, so I'm not asking about making it more secure. I'm just asking if there's a way to do this in bulk so I don't have to go through this every time. I read the BOL article but I can't figure out how to apply that to my cross-domain situation in my job. Thanks for your help.
September 24, 2019 at 7:09 pm
I take it that keeping your packages in their corresponding source control branches is not possible?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 24, 2019 at 7:14 pm
It is not yet, but that's what I'm trying to get to. If I can just get my Dev & QA environments to be sync'd to my PROD again, I'm hoping to do just as you suggest. It just requires coordination with some other teams, so I'm trying to Band-Aid my way through.
September 24, 2019 at 7:49 pm
Fair enough. You could try comparing package XML before and after setting DontSaveSensitive, using something like WinMerge, to see whether this is a change you can make using find/replace in a text editor.
If not, I do not know of any quick way of making this change in bulk.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 24, 2019 at 8:42 pm
That sounds like a really viable option! I'll give it a shot and report back on the results. Thanks so much, Phil!
September 25, 2019 at 11:25 am
Have a look here in the last part you can simply open the dtsx with some editor and search for the ProtectionLevel and replace it in the dtsx files accordingly. However you have to change the project protection settings, too. Can't recall right now which file it was but it's within the SSIS Package, XML and same thing to look for. Definitely much faster than having to wait for each dtsx to open up in BIDS / VS.
When you're done editing don't forget to rebuild the solution and deploy the new .ispac file.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply