Changing DelayValidation on multiple SSIS packages

Question

I have a project with about 150 packages in SSIS 2012. One of them is the entry point and the rest are called somewhere down the line from the main. Each of them creates its own table in the destination if one does not exist. I've found that I need to set DelayValidation to True for this to work, but loathe the job of doing it 150 times. Is there a way to have this property propagate or anything other than making a change to all the packages?

Answer 1

You can make this change by using the .NET library. Below is a PowerShell script that looks at all the packages in a given folder. If the DelayValidation property is false, then it will change it to True and save the package.

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.ManagedDTS") | Out-Null
$folder = "C:\sandbox\StackOverflow\StackOverflow\obj\Development"

$app = New-Object Microsoft.SqlServer.Dts.Runtime.Application

foreach($current in (Get-ChildItem $folder -Filter "*.dtsx").FullName)
{
    Write-Host $current

    $package = $app.LoadPackage($current, $null)
    if ($package.DelayValidation -eq $false)
    {
        $package.DelayValidation = $true
        $app.SaveToXml($package, $null)
    }
}

Caveats

• Never run automated code without testing that you have version control.
• This only changes the Package's DelayValidation. Each Container and Task have their own DelayValidation property that may need to be updated

2017+ note

A user noted

SaveToXml function needs 3 parameter in SQL Server 2017.

That would make this SaveToXml look like

$app.SaveToXml($current, $package, $null)

The Documentation indicates this has been so since 2016.

They further noted,

As a Developer without installing SQL Server like me, need located your Microsoft.SQLServer.ManagedDTS.dll file explicitly

That would replace the first line's LoadWithPartialName to LoadFile with an explicit path like

 ([Reflection.Assembly]::LoadFile("C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\CommonExtensions\Microsoft\SSIS\140\Binn\Microsoft.SqlServer.ManagedDTS.dll") | Out-Null)

Note the exact path will vary based on your installed tooling. I tend to use the dir /s /b (directory, search subfolders, bare format) command to find things so

C:\>cd "\Program Files (x86)"
C:\Program Files (x86)>dir /s /b Microsoft.SqlServer.ManagedDTS.dll

C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\Binn\Microsoft.SqlServer.ManagedDTS.dll
C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\PublicAssemblies\SSIS\150\Microsoft.SqlServer.ManagedDTS.dll

Here we can see I have two ManagedDTS.dll both for the 150 (2019?) release of SQL Server