November 18, 2014 at 5:35 am
Hi there,
I am using Variables to configure a lot in an SSIS Packages.
Over the years you add new variables that are usefull to your default-package.
However to use those in "older" packages you have to open add them manually.
Do you know of a way (e.g. skript) to add a set of variables to every SSIS-package in a folder ?
Christian
November 18, 2014 at 10:20 am
I know it can be done because I believe both PragmaticWorks BIExpress and Myst by Varigence allow you to inject code into existing SSIS packages. I'd recommend looking into those products before I tried to write the code myself. Myst is available as a monthly subscription so you could use it for as long as you need and then cancel your subscription. Myst does require you to learn BIML and the way to do it is with transformers. You can read about it here, http://bimlscript.com/Walkthrough/Details/68, and there's a webinar recording available here, https://www.youtube.com/watch?v=-lJ7dc5eQWI
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 18, 2014 at 1:36 pm
Thank you for your suggestions. I will look into them.
I finally had a google-search that didn't show me examples how to add variables to a skript-task
http://www.pawlowski.cz/2012/06/manipulating-ssis-packages-powershell/
He seems to use powershell to add variables.
I will look into all of it this week
Cheers
Christian
January 12, 2015 at 8:04 am
I used the code from the site mentioned above and modified it a little bit.
I wanted to go through all our dtsx-Packages and at the Variable if it did NOT exists.
In this example with Evaluate as Expression
Basically for each node in the xml you create an Attribute Block in the code.
If you want to add more then one variable at the time, you just repeat the code between
##MY_VARIABLE_NAME START
##MY_VARIABLE_NAME END
#Get List of all relevat SSIS Packages, Recursive does not allow specification of file extention
$files = Get-ChildItem -Recurse "C:\ssis_folder\"
#process file by file
foreach($file in $files)
{
#check if it is dtsx, as recursion did not filter. just change dtsx packages
if ($file -like "*.dtsx")
{
#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:Variables[1]", $mng)
##MY_VARIABLE_NAME START
#use XPath query to get eventual existing SSISTaskID parameter
$param = $dts.SelectSingleNode("/DTS:Executable/DTS:Variables/DTS:Variable[@DTS:ObjectName='MY_VARIABLE_NAME'][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", "Variable", "www.microsoft.com/SqlServer/Dts")
$attr = $dts.CreateAttribute("DTS", "CreationName", "www.microsoft.com/SqlServer/Dts")
$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)
##EvaluateAsExpression
$attr = $dts.CreateAttribute("DTS", "EvaluateAsExpression", "www.microsoft.com/SqlServer/Dts")
$attr.Value = "True"
$param.Attributes.Append($attr)
##Expression
$attr = $dts.CreateAttribute("DTS", "Expression", "www.microsoft.com/SqlServer/Dts")
$attr.Value = ""expression_text" + @[User::other_variable] + ""
$param.Attributes.Append($attr)
##IncludeInDebugDump
$attr = $dts.CreateAttribute("DTS", "IncludeInDebugDump", "www.microsoft.com/SqlServer/Dts")
$attr.Value = "2345"
$param.Attributes.Append($attr)
##Namespace
$attr = $dts.CreateAttribute("DTS", "Namespace", "www.microsoft.com/SqlServer/Dts")
$attr.Value = "User"
$param.Attributes.Append($attr)
#parameter name
$attr = $dts.CreateAttribute("DTS", "ObjectName", "www.microsoft.com/SqlServer/Dts")
$attr.Value = "MY_VARIABLE_NAME"
$param.Attributes.Append($attr)
#Parameter Value property - setting the parameter value
$paramValue = $dts.CreateElement("DTS", "VariableValue", "www.microsoft.com/SqlServer/Dts");
#setting attributes of Parameter Value Property
$attr = $dts.CreateAttribute("DTS", "DataType", "www.microsoft.com/SqlServer/Dts")
$attr.Value = "8"
$paramValue.Attributes.Append($attr)
#Set the value of the ParameterValue property
$paramValue.InnerText = "expression_text_value"
#add the property to the Parameter
$param.AppendChild($paramValue)
#Add the Parameter to the Params collection
$params.AppendChild($param)
}
##MY_VARIABLE_NAME END
################################################################################################
# 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()
}
}
What I am still working on is to modify existing Nodes
I would like to change existing Connection-Strings.
When I figured it out, I will post it here again
Delete this one, if exists
<DTS:PropertyExpression
DTS:Name="InitialCatalog">@[User::old_variable]</DTS:PropertyExpression>
Add this one, if not exists
<DTS:PropertyExpression
DTS:Name="ConnectionString">@[User::new_variable]</DTS:PropertyExpression>
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply