modify XML config file with POWERSHELL

• Lord Slaagh

  SSCertifiable

  Points: 5766

  More actions

  April 11, 2016 at 1:47 pm

  #313166

  Hello,

  I am trying to update a connection string in an XML file using POWERSHELL. I have the following code that is not working. I have a code snippet for the XML file and my PS script below.

  Any help would be appreciated! BigGrin

  POWERSHELL script

  # Read configuration file in a XML variable

  $webConfig = 'C:\MyXML.dtsConfig'

  $doc = (Get-Content $webConfig) -as [Xml]

  $root = $doc.get_DocumentElement();

  $newCon = $root.Configuration.add.ConfiguredValue.Replace('Data Source=NYD','Data Source=MILANOMOSH');

  $root.Configuation.add.ConfiguredValue = $newCon

  $doc.Save($webConfig)

  XML File OUTPUT

  <?xml version="1.0"?>

  <DTSConfiguration>

  <DTSConfigurationHeading>

  <DTSConfigurationFileInfo GeneratedBy="NA\John.Doe" GeneratedFromPackageName="MP_FEED_MAIN" GeneratedFromPackageID="{E80FF572-65D9-49D3-9B19-B5975A29F663}" GeneratedDate="4/11/2016 2:10:27 PM" />

  </DTSConfigurationHeading>

  <Configuration ConfiguredType="Property" Path="\Package.Connections[NYD.DATABASE].Properties[ConnectionString]" ValueType="String">

  <ConfiguredValue>Data Source=NYD;Initial Catalog=DBNAME;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue>

  </Configuration>

  </DTSConfiguration>

  ERROR MESSAGE

  You cannot call a method on a null-valued expression.

  At line:6 char:58

  + $newCon = $root.Configuration.add.ConfiguredValue.Replace <<<< ('Data Source=NYD','Data Source=MANDOMOSH');

  + CategoryInfo : InvalidOperation: (Replace:String) [], RuntimeException

  + FullyQualifiedErrorId : InvokeMethodOnNull

  Property 'Replace' cannot be found on this object; make sure it exists and is settable.

  At line:7 char:41

  + $root.Configuration.add.ConfiguredValue. <<<< Replace = $newCon

  + CategoryInfo : InvalidOperation: (Replace:String) [], RuntimeException

  + FullyQualifiedErrorId : PropertyNotFound

  The are no problems, only solutions. --John Lennon

• Gary Varga

  SSC Guru

  Points: 82166

  More actions

  April 12, 2016 at 5:28 pm

  #1871567

  I am sure that the add method needs more than just:

  $root.Configuration.add

  Unfortunately, I am away from a computer.

  Gaz

  -- Stop your grinnin' and drop your linen...they're everywhere!!!

• Lord Slaagh

  SSCertifiable

  Points: 5766

  More actions

  April 13, 2016 at 10:22 am

  #1871786

  Thanks for your response. I resolved this issue in the following blog...

  http://www.sqlservercentral.com/Forums/Topic1776398-1351-1.aspx#bm1776799

  The are no problems, only solutions. --John Lennon