Monday, October 4, 2010

SSIS Package Configurations

By Jim Bennett

Recently I was asked to verify whether a directory full of SSIS packages all had their package configurations enabled. So instead of opening each one manually I decided to write a Powershell script to do it for me. In each SSIS package file there is an XML node that is designated for the package configurations. The XML looks like this for disabled package configurations:

<DTS:Property DTS:Name="EnableConfig">0</DTS:Property>
and this for enabled package configurations:
<DTS:Property DTS:Name="EnableConfig">-1</DTS:Property>

The Powershell script is rather straightforward and could easily be modified to change this setting.

Set-ExecutionPolicy Unrestricted
## ScriptName: SearchForPackagesWithConfigurationsOff
## WrittenBy: Jim Bennett – CapstoneBI
## WrittenDate: 09/30/2010
## Website:
## Purpose: Search for SSIS packages in a folder with their configurations setting turned off
if ([String]$args[0] -eq "")
{write-output "Usage: $($MyInvocation.InvocationName) DirectoryToSearch"}
$sourcefolder = [String]$args
if(!$sourcefolder.EndsWith("\")) {$sourcefolder+="\"}
$configurationSearchString = '<DTS:Property DTS:Name="EnableConfig">0</DTS:Property>'
$includedFileTypes = "*.dtsx"
get-childitem $($sourcefolder+"*") -include $includedFileTypes ForEach-Object {
if ($(Select-String -quiet $configurationSearchString $($sourcefolder + $_.Name)) -eq "true")
{write-output $_.Name}


No comments:

Post a Comment