April 8, 2016 at 2:05 pm
Hello SSC,
The objective here is to dynamically update all dtsconfig files for deployment on three tier architecture (dev, UAT, prod), to make deployment easier and more efficient. We have several SSIS packages which have individual config files per package (OH NO!).
My idea is to modify the elements in the dtsconfig to eventually accept ServerName and DB parameters from SSIS variables. I then want to pass those variables into a PS script to modify all config files in a specified directory. For now, I just want to see if I can actually make this work in one config file with hard-coded values.
The following script runs successfully but the dtsconfig file does not accept any of my changes. What am I doing wrong? :unsure:
Any help would be greatly appreciated!
# Set-ExecutionPolicy RemoteSigned
ls C:\.dtsConfig | foreach {
$file = $_."C:\*.dtsConfig"
$xml = [xml](Get-Content $file)
$UpdateStr = "kjdfhgkjdfhgkdjfhg"
@($xml.SelectNodes('//GeneratedBy')) | %{
$_.'#text'=($_.'#text' -replace '_\d+',$UpdateStr)
}
$xml.Save($file)
}
The are no problems, only solutions. --John Lennon
April 8, 2016 at 4:26 pm
I am not sure what you mean by "dtsconfig file does not accept any of my changes" but I would first look at the type and value of $file. Let us know!!!
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
April 9, 2016 at 2:12 pm
Provide a sample configuration file so we can see the structure of your XML to what node you are trying to modify.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 11, 2016 at 7:14 am
Ok here is the dtsconfig file. I am trying to gain the ability to update the server name and database name.
My idea is to pass Servername and DB name variables from SSIS into this Powershell script, which will the then update the XML file.
<?xml version="1.0"?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="NA\EddieVH" GeneratedFromPackageName="FEED_MAIN" GeneratedFromPackageID="{E80FF572-65D9-49D3-9B19-B99999999}" GeneratedDate="4/8/2016 11:28:25 AM"/>
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package.Connections[EDDIEVH.DataBaseNAME].Properties[InitialCatalog]" ValueType="String"><ConfiguredValue_>DataBaseNAME</ConfiguredValue_>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[EDDIEVH.DataBaseNAME].Properties[ServerName]" ValueType="String"><ConfiguredValue_>EDDIEVH</ConfiguredValue_>
</Configuration></DTSConfiguration>
The are no problems, only solutions. --John Lennon
April 11, 2016 at 10:08 am
Take a look at this script:
Manipulating SSIS Packages using PowerShell
[/url]
The above script utilizes XQuery in PowerShell so it can be more readable and offer more flexibility.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 11, 2016 at 11:47 am
Thank you for your response, but the link is not working.
The are no problems, only solutions. --John Lennon
April 12, 2016 at 10:21 am
Ok, I got it. This is how it's done.
I want to thank everyone for trying to help me out. SSC FTW!!!!
$ServerName = "MilanoMosh"
$ConfigFile = "C:\MyFile.dtsConfig"
function Edit-XmlNodes {
param (
[xml] $doc = $(throw "doc is a required parameter"),
[string] $xpath = $(throw "xpath is a required parameter"),
[string] $value = $(throw "value is a required parameter"),
[bool] $condition = $true
)
if ($condition -eq $true) {
$nodes = $doc.SelectNodes($xpath)
foreach ($node in $nodes) {
if ($node -ne $null) {
if ($node.NodeType -eq "Element") {
$node.InnerXml = $value
}
else {
$node.Value = $value
}
}
}
}
}
$xml = [xml](Get-Content $ConfigFile)
# <file><foo attribute="bar" attribute2="bar" attribute3="bar" /></file>
Edit-XmlNodes $xml -xpath "/DTSConfiguration/Configuration/ConfiguredValue " -value $ServerName
$xml.save($ConfigFile)
# <file><foo attribute="new value" attribute2="bar" attribute3="bar" /></file>
The are no problems, only solutions. --John Lennon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply