Created
March 9, 2020 19:17
-
-
Save paschott/713244a9c4795091cc99e3332084ea48 to your computer and use it in GitHub Desktop.
Changes SSIS Protection Level for all packages and project
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#PowerShell script | |
################################ | |
########## PARAMETERS ########## | |
################################ | |
$projectFolder = "C:\SSIS\myProject\myProject" | |
$dtutilPath = "C:\Program Files\Microsoft SQL Server\130\DTS\Binn\dtutil.exe" | |
# The number changes per SQL Server version | |
# 130=2016, 120=2014, 110=2012 | |
# Also check the drive where SQL Server is | |
# installed | |
################################################# | |
########## DO NOT EDIT BELOW THIS LINE ########## | |
################################################# | |
clear | |
Write-Host "=========================================================================================" | |
Write-Host "== Used parameters ==" | |
Write-Host "=========================================================================================" | |
Write-Host "Project Folder :" $projectFolder | |
Write-Host "dtutil Path :" $dtutilPath | |
Write-Host "=========================================================================================" | |
###################################### | |
########## Check parameters ########## | |
###################################### | |
# Test whether the paths are filled | |
# and exists. | |
if ($projectFolder -eq "") | |
{ | |
Throw [System.Exception] "Project path parameter is mandatory" | |
} | |
elseif (-Not (Test-Path $projectFolder)) | |
{ | |
Throw [System.IO.FileNotFoundException] "Project path $($projectFolder) doesn't exists!" | |
} | |
elseif (-Not $projectFolder.EndsWith("\")) | |
{ | |
# Make sure path ends with \ for command | |
$projectFolder = $projectFolder + "\" | |
} | |
if ($dtutilPath -eq "") | |
{ | |
Throw [System.Exception] "dtutil parameter is mandatory" | |
} | |
elseif (-Not (Test-Path $dtutilPath)) | |
{ | |
Throw [System.IO.FileNotFoundException] "dtutil not found at $($dtutilPath)" | |
} | |
############################################# | |
########## dtutil for loop command ########## | |
############################################# | |
# In this script we are executing dtutil.exe | |
# Perhaps a bit quick & dirty, but more quick | |
# than dirty. It changes 100 packages within | |
# seconds. | |
$command = "/C FOR %p IN (""$($projectFolder)*.dtsx"") DO dtutil.exe /file ""%p"" /encrypt file;""%p"";0 /quiet" | |
Write-Host "Editing packages in $($projectFolder)... " -NoNewline | |
# Open the command prompt (hidden) and execute | |
# dtutil.exe with the parameters from above. | |
Start-Process "C:\Windows\System32\cmd.exe" -ArgumentList $command -WindowStyle Hidden -Wait | |
Write-Host "Done." | |
########################################## | |
########## Editing project file ########## | |
########################################## | |
# Find the project file. There should be | |
# only one dtproj file. | |
$projectFile = get-childitem $projectFolder -name -filter *.dtproj | |
Write-Host "Editing project file $($projectFile)... " -NoNewline | |
# Edit the project file and replace the | |
# protection level. First replace is for | |
# all the packages and the second replace | |
# is for the project itself. It uses a | |
# regular expression for the replace, but | |
$projectFilePath = Join-Path -Path $projectFolder -ChildPath $projectFile | |
(Get-Content $projectFilePath) -replace 'ProtectionLevel">[0-9]', 'ProtectionLevel">0' -replace 'ProtectionLevel="[A-Za-z]*"', 'ProtectionLevel="DontSaveSensitive"' | Set-Content $projectFilePath | |
Write-Host "Done." | |
############################## | |
########## Finished ########## | |
############################## | |
# Finished editing packages and project file | |
Write-Host "Finished editing $($projectFile) and $((get-childitem $projectFolder -name -filter *.dtsx).Count) packages" -ForegroundColor Magenta |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment