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
Everyone has a plan until they get punched in the mouth. --Mike Tyson
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 11, 2016 at 1:13 pm
I edited my post to reflect the error message.
My bad 😀
Everyone has a plan until they get punched in the mouth. --Mike Tyson
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply