Probably you have come to situation when you have a SSIS project with higher count of packages and you need to add for example a parameter or variable to all or most of the packages. Normally you have to open each package and add variable/parameter to all packages one by one and specify all the properties in each package.
Using the PowerShell will save you a lot of manual work and tons of clicks inside the SQL Server Data Tools (SSDT) or Business Intelligence Development Studio (BIDS) as PowerShell will allow you to automate the manual work. It’s very easy to manipulate the packages by PowerShell as SSIS packages are pure XML Files and PowerShell has a good XML support.
For writing, testing and running PowerShell script I suggest you download and install PowerGUI which has great IntelliSense support and is probably best PowerShell editor available. Also I suggest you to install the Run AddOn which allows you to execute only selected part of script.
Also before you start executing the scripts, ensure that you have properly set ExecutionPolicy to allow script execution. You have to run it with elevated Administrator privileges.
Enable Local not signed Script Execution Set-ExecutionPolicy RemoteSigned
Example how to manipulate SSIS using PowerShell
In this example we will manipulate SSIS 2012 packages and we will add a new parameter to the packages.
First thing you have to do is to retrieve list of SSIS package to be processed. This can be easily done by the Get-ChildItem cmdlet.
$files = Get-ChildItem "C:MySSISProjects*.dtsx"
Once we have the list of file to be processed we can start processing file by file using the foreach structure. In this example we will add a SSISTaskID package parameter.
Get List of all relevat SSIS Packages
$files = Get-ChildItem "C:MyProjectsMySSISProject*.dtsx"
#process file by file
foreach($file in $files)
{
#get the content of SSIS package as XML
$dts = [xml](Get-Content $file.FullName)
#create XmlNamespaceManager
$mng = [System.Xml.XmlNamespaceManager]($dts.NameTable)
#add a DTS namespace to the XmlNamespaceManager
$mng.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts")
#use XPath query to get DTS:PackageParameters node
$params = $dts.SelectSingleNode("/DTS:Executable/DTS:PackageParameters[1]", $mng)
#use XPath query to get eventual existing SSISTaskID parameter
$param = $dts.SelectSingleNode("/DTS:Executable/DTS:PackageParameters/DTS:PackageParameter[@DTS:ObjectName='SSISTaskID'][1]", $mng)
if ($param -eq $null) #parameter does not exists
{
#Create New Package parameter with prefix DTS and correct namespace uri
$param = $dts.CreateElement("DTS", "PackageParameter", "www.microsoft.com/SqlServer/Dts")
$attr = $dts.CreateAttribute("DTS", "CreationName", "www.microsoft.com/SqlServer/Dts")
$param.Attributes.Append($attr)
#Parameter Data Type
$attr = $dts.CreateAttribute("DTS", "DataType", "www.microsoft.com/SqlServer/Dts")
$attr.Value = "3" #DataType = 3 is equal to integer
$param.Attributes.Append($attr)
##Description
$attr = $dts.CreateAttribute("DTS", "Description", "www.microsoft.com/SqlServer/Dts")
$attr.Value = "This is a TaskID"
$param.Attributes.Append($attr)
#DTSID - unique GUID for created parameter
$attr = $dts.CreateAttribute("DTS", "DTSID", "www.microsoft.com/SqlServer/Dts")
$attr.Value = "{" + [System.Guid]::NewGuid().toString().ToUpper() + "}" #Each parameter has unique GUID in SSIS package, so let's generate one
$param.Attributes.Append($attr)
#parameter name
$attr = $dts.CreateAttribute("DTS", "ObjectName", "www.microsoft.com/SqlServer/Dts")
$attr.Value = "SSISTaskID"
$param.Attributes.Append($attr)
#Parameter Value property - setting the parameter value
$paramValue = $dts.CreateElement("DTS", "Property", "www.microsoft.com/SqlServer/Dts");
#setting attributes of Parameter Value Property
$attr = $dts.CreateAttribute("DTS", "DataType", "www.microsoft.com/SqlServer/Dts")
$attr.Value = "3"
$paramValue.Attributes.Append($attr)
$attr = $dts.CreateAttribute("DTS", "Name", "www.microsoft.com/SqlServer/Dts")
$attr.Value = "ParameterValue"
$paramValue.Attributes.Append($attr)
#Set the value of the ParameterValue property
$paramValue.InnerText = "0"
#add the property to the Parameter
$param.AppendChild($paramValue)
#Add the Parameter to the Params collection
$params.AppendChild($param)
}
################################################################################################
# Once the Parameter is created it is time to write the package back to disk
################################################################################################
#Create XmlWriterSettings as we want to format the SSIS package properly
[System.Xml.XmlWriterSettings] $settings = New-Object -TypeName System.Xml.XmlWriterSettings
$settings.Indent = $true #Indent child nodes
$settings.NewLineOnAttributes = $true #put each attribude on separate line
#Create XmlWriter with appropriate settings and path to overwrite the original package
[System.Xml.XmlWriter]$writer = [System.Xml.XmlWriter]::Create($file.FullName, $settings)
#Save the package using the XmlWriter
$dts.Save($writer)
#Close the writer
$writer.Close()
}
DTS:PackageParameters node under the DTS:Executable node.
For correct querying we have to use XmlNameSpaceManager as all the nodes in SSIS Package are prefixed with DTS name space.
For checking whether the parameter we would like to add exists or not we again use XPath with appropriate filters to find it by Name.
Then if the parameter doesn't exists, we create a new one. What attributes and child nodes are necessary for such parameter you find out easy. Create the parameter using BIDS or SSDT in one of the SSIS packages, then take a look into the source code of the package and you will be able to find all the information necessary. For example in our case the parameter node in the source XML looks as below:
<DTS:PackageParameter
DTS:CreationName=""
DTS:DataType="3"
DTS:Description="This is a TaskID"
DTS:DTSID="{3252C760-114A-4E8F-86EB-D3E6BAACA0E4}"
DTS:ObjectName="SSISTaskID">
<DTS:Property
DTS:DataType="3"
DTS:Name="ParameterValue">0</DTS:Property>
</DTS:PackageParameter>
So we had to create a PackageParameterNode with CreationName, DataType, Description, DTSID and ObjectName attributes and also with child node Property, which represents default value of the parameter.
As each parameter, variable or whatever part in SSIS has it's own unique DTSID property, which is GUID, we have used the [System.Guid]::NewGuid() method to generate unique DTSID for our parameter.
Finally once the changes into the SSIS package are applied, it is necessary to write it back to the file system. By default the source of the SSIS package is indented and and each attribute is written on separate line. SSIS packages are written in that way to be more human readable and allow an easy processing and comparison by versioning systems.
To achieve the same formatting we have to create and use instance of the XmlWriter class with appropriate XmlWriterSettings. As you can see from the script we set Indent and NewLineOnAttributes properties to true.
Once the writer is constructed, we can write the package back to the file system. It is also important to close the writer once the saving is done otherwise you will keep open handles to the processed files.
In the example we are also overwriting the original package as we specify the $file.FullName as path for the XmlWriter class. If we want, we can provide whatever name for the package and save it under different name. In case we would like to add a "_modified" suffix to the file name, we can use:
System.IO.Path]::Combine($file.DirectoryName, $file.BaseName + "_modified") + $file.Extension
Conclusion
As we can see from the example, the SSIS manipulation using PowerShell is quite easy and after a small training with writing this modification also very quick and will save you a lot of manual work if you have to modify a higher count of packages. You can alter the above script slightly to do nearly whatever modification is necessary.
This example was done on SSIS 2012 package source, but can be relatively easy applied also on SSIS 2005 and SSI 2005 although the XML source of those packages are not so easy readable as the source of SSIS 2012.
Pavel's SQL Pages 
