Change Protection Level for all packages at once

Case
I created dozens of packages in my project but I forgot to change the default Protection Level in the project properties from "EncryptSensitiveWithUserKey" to "DontSaveSensitive". Now I have to change all packages one by one. Is there an alternative? I tried search and replace in the XML, but I can't find the Protection Level property.

Solution
Of course the best option is to prevent this from happening by setting the default before you start. You can do this in the properties of the project. All new packages will then inherit the Protection Level from the project.

Setting Protection Level on project

First, when trying to search and replace in the XML code of the packages you will notice that you cannot find the default 'EncryptSensitiveWithUserKey' which makes it hard to replace.

Default Protection Level is not in package

Secondly, the Protection Level is also stored in the Visual Studio project file (*.dtproj). When you open a package in design mode and press the save button it also updates metadata in the project file.

Protection Level in project file as well

Solution A
Good old Command Prompt to the rescue! The dtutil Utility can do the package conversion for you. If you are afraid of the Command Prompt or even never heard about it, then don't use this solution.

1) Command Prompt
Open a Command Prompt and use CD (Change Directory) command to navigate to your folder with packages.

Navigate to your project folder with packages

2) Foreach Loop Container in DOS
Now you can call the dtutil Utility for each package in that folder with something similar as a Foreach Loop Container:
FOR %p IN (*.dtsx) DO dtutil.exe /file "%p" /encrypt file;"%p";0 /quiet

The colors explain the command

3) Execute
When you execute the command, dtutil Utility will quickly change the Protection Level of all your packages.

101 packages changed within 5 seconds. Try that in Visual Studio!

4) Project Protection Level
If you haven't already done it, change the Protection Level in the Project Properties. See second screenshot of this blog post.

5) dtproj file
Now the project and all its packages have the same Protection Level, but the project doesn't now that yet. If you try to execute a package it will complain about the Protection Level inconsistencies.

Failed to execute the package or element. Build errors were encountered.

Error : Project consistency check failed. The following inconsistencies were detected:
 MyPackage000.dtsx has a different ProtectionLevel than the project.
 MyPackage001.dtsx has a different ProtectionLevel than the project.

To update the dtproj file you have to open all packages and then Rebuild the project. This will update the project file. Now you can execute the packages without the consistency error.

Open all packages and rebuild the project

Solution B
Good old PowerShell to the rescue! This PowerShell script does the same as above, but also changes the project file. So no manual labour at all. Because the dtutil utility was so fast, I didn't edit the packages with .net libraries. It just executes dtutil in a hidden window.

The script is thoroughly tested for SSIS 2012-2016 from 'EncryptSensitiveWithUserKey' to 'DontSaveSensitive'. Other situations require more testing. Make sure to keep a copy of your project before using this script and let me know which situations require some more attention.

Change the protection level of the entire project in seconds

#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

SSIS Appetizer: XML source is already sorted

Case
I have a large XML file with Orders and Onderlines which I want to (merge) join into a new destination. To join the two outputs I need to order them, but the sort transformation takes too much time. Is there a faster alternative?

XML Source with two joined outputs

Solution
The solution is surprisingly quite simple: the outputs are already sorted and you only have to tell SSIS that (Similar to a source with order by in the query).

For XML files with multiple levels (first for orders and second for orderlines) like below, SSIS will create two output ports.

XML Sample

The outputs will have an extra bigint column which allows you to connect the orderlines to the correct order.

Two outputs with additional ID column

Instead of using these ID columns in the SORT transformations, you can also use the advanced editor of the XML source to tell SSIS that these columns are already sorted. Right click the XML source and choose 'Show Advanced Editor...'.

Show Advanced Editor...

Then go to the last page 'Input and Output Property' and select the Orderline output. In the properties of this output you can tell SSIS that the output is sorted.

Set IsSorted to true

Next expand OrderLine and then Output Columns and click on the additional ID column 'Order_Id'. In its properties locate the SortKeyPosition and change it from 0 to 1.

Set SortKeyPosition to 1

Repeat this for the second output called 'Order' and then close the advanced editor. If you still have the SORT transformations, you will notice the yellow triangle with the exclamation mark in it. It tells you that the data is already sorted and that you can remove the SORT transformations.
And if you edit the Data Flow Path and view the metadata you will see that the column is now sorted.

Sorted! Remove the SORT transformations!

Conclusion
The solution is very simple and perhaps this should have been the default sort key position anyway? It smells like a bug to me...

No sorts!