Skip to content

Instantly share code, notes, and snippets.

@paschott
Created March 9, 2020 19:17
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save paschott/713244a9c4795091cc99e3332084ea48 to your computer and use it in GitHub Desktop.
Save paschott/713244a9c4795091cc99e3332084ea48 to your computer and use it in GitHub Desktop.
Changes SSIS Protection Level for all packages and project
#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