April 11, 2016 at 12:52 pm
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! 😀
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
April 11, 2016 at 1:07 pm
SQLTougherGuy (4/11/2016)
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! 😀
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>
Saying that something is 'not working' is not particularly helpful to others in tracking down what the issue is.
Can you expand on this to indicate where the problem is and what, if any, error messages you are receiving?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 11, 2016 at 1:13 pm
I edited my post to reflect the error message.
My bad 😀
The are no problems, only solutions. --John Lennon
April 11, 2016 at 1:19 pm
I am no PoSh / XML expert, but I did find this[/url] blog which seems to have everything covered pretty well. Maybe it will do what you need.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply