How to add variables to SSIS-Packages per Batch

  • 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

  • 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

  • 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

  • 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