How to update the Data Source connection string using PowerShell in the XML file

Question

I am trying to automate to update the data source in a web.config file with new values using PowerShell.

Here is my connection string:

<connectionStrings>
    <add name="abcLogging" connectionString="Data Source=(local)\abc; Trusted_Connection=True; Persist Security Info=True; Enlist=true; Initial Catalog=abcLogDB" providerName="System.Data.SqlClient" />
</connectionStrings>

PowerShell script:

$newstring = '"Data Source=(test)\abc; Trusted_Connection=True; Persist Security Info=True; Enlist=true; Initial Catalog=abcLogDB" '
$oldstring = '"Data Source=(local)\abc; Trusted_Connection=True; Persist Security Info=True; Enlist=true; Initial Catalog=abcLogDB" '
$XmlDocument = [xml](Get-Content "D:\abc\Web.config");
$value = $XmlDocument.configuration.connectionStrings.add.connectionstring 
$value = $value -replace "$oldstring","$newstring" | Set-Content -PassThru

I have got the following error when I ran the above script.

The regular expression pattern "Data Source=(local)\abc; Trusted_Connection=True;
Persist Security Info=True; Enlist=true; Initial Catalog=abcLogDB"  is not valid.
At line:5 char:1
+ $value = $value -replace "$oldstring","$newstring" | Set-Content -Pas ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: ("Data Source=(l...og=OnityLogDB" :String) [], RuntimeException
    + FullyQualifiedErrorId : InvalidRegularExpression

Can't reproduce. The code you posted doesn't throw the error you claim it does. — Ansgar Wiechers
– Ansgar Wiechers, Commented Apr 29, 2019 at 9:53
I am still having the same issue, i couldn't update my connection string with the above script — Hari Krishna
– Hari Krishna, Commented Apr 29, 2019 at 10:35
I tested the code you posted with the data sample you posted, and it does not throw the error shown in your question. Did you try from a clean PowerShell instance (run powershell.exe -NoProfile -NoExit from CMD)? Does the problem occur there as well? — Ansgar Wiechers
– Ansgar Wiechers, Commented Apr 29, 2019 at 11:02

Rich Moss · Accepted Answer · 2019-04-30 15:55:03Z

1

Welcome to StackOverflow, Hari Krishna! There are a few things going on with your script.

There are too many quotes in your old and new strings - the old string will not be found. Generally, single quotes should be used unless there is an embedded $variableName to be expanded with its value.
The parentheses are being treated as regular expression special characters because you are using the -replace syntax. The [string] object's .Replace method can be used for a simple replace without regular expression.
The Set-Content command doesn't know what file to act on. But you don't really want to set the content of the file to only be $value. That would get you a file with none of the XML content and only the connection string.

Here's a script that should do what you want:

$configFile = 'C:\temp\web.config'

Set-Content -Path $configFile -Value @"
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<connectionStrings>
    <add name="abcLogging" connectionString="Data Source=(local)\abc; Trusted_Connection=True; Persist Security Info=True; Enlist=true; Initial Catalog=abcLogDB" providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
"@

$newstring = 'Data Source=(test)\abc; Trusted_Connection=True; Persist Security Info=True; Enlist=true; Initial Catalog=abcLogDB'
$oldstring = 'Data Source=(local)\abc; Trusted_Connection=True; Persist Security Info=True; Enlist=true; Initial Catalog=abcLogDB'

$XmlDocument = [xml](Get-Content $configFile);
$XmlDocument.configuration.connectionStrings.add | %{
    If($_.connectionString -eq $oldstring){
        $_.connectionString = $newstring
    }
}
$XmlDocument.Save($configFile)
Get-Content -Path $configFile

EDIT: corrected issues found during additional testing.

edited Apr 30, 2019 at 15:55

answered Apr 29, 2019 at 15:00

Rich Moss

2,4041 gold badge15 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Hari Krishna Over a year ago

Thank you Rich for your detailed response, I have got the following error, Cannot convert argument "0", with value: "abc\Web.config", for "WriteContentTo" to type "System.Xml.XmlWriter": "Cannot convert the "D:\abc\Web.config" value of type "System.String" to type "System.Xml.XmlWriter"." At line:6 char:1 + $XmlDocument.WriteContentTo($ConfigFile) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodException + FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument

Hari Krishna Over a year ago

When i add $XmlDocument.Save($ConfigFile) instead of $XmlDocument.WriteContentTo($ConfigFile), no error however, the value inside the file doesn't get updated

Rich Moss Over a year ago

That's odd. What version of Powershell are you using? Try running $PSVersionTable at a Powershell prompt and reply with the results. I've successfully tested the script above in PSVersion 4.

Hari Krishna Over a year ago

My version 5.1 PowerShell

Rich Moss Over a year ago

Did you copy the script above exactly as written? The error message indicates that $configFile has an incorrect value. Also, I concur with @Ansgar-Wiechers' suggestion to try a new Powershell window.

|

Collectives™ on Stack Overflow

How to update the Data Source connection string using PowerShell in the XML file

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related