Set-ISConnectionString

Apr 20, 2011 at 9:00 PM

I'm trying to change the server connection inside the SSIS using Powershell, to make it simple, let's there is only one connection, and I want to change from server "ABC" to "XYZ". Is Set-ISConnectionString the right cmdlet to use ?

I was looking at the help (at http://www.sqlpsx.com/) and it gave an example as follow:

$package = Get-ISPackage 'msdb\sqlpsx2' Z002
set-ISConnectionString $package @{SSISCONFIG='.\SQL2K8'}

I can understand the 1st statement, but in the 2nd statement, I just don't understand the meaning of:

@{SSISCONFIG='.\SQL2K8'}

Can someone explain to me ? and how the 2nd statement will look like in my case ?

Thanks in advance,
Jimmy
Coordinator
Apr 21, 2011 at 1:55 AM
If you look at the function definition in SSIS.psm1:

function Set-ISConnectionString
{
param(
[Parameter(Position=0, Mandatory=$true)] $package,
[Parameter(Position=1, Mandatory=$true)] [hashtable]$connectionInfo
)

Write-Verbose "Set-ISConnectionString"

foreach ($i in $connectionInfo.GetEnumerator())
{
$name = $($i.Key); $value = $($i.Value);
Write-Verbose "Set-ISConnectionString name:$name value:$value "
$connectionManager = $package.connections | where {$_.Name -eq "$name"}
Write-Verbose "Set-ISConnectionString connString1:$($connectionManager.ConnectionString)"
if ($connectionManager)
{
$connString = $connectionManager.ConnectionString
Write-Verbose "Set-ISConnectionString connString:$connString"
$connString -match "^Data Source=(?<server>[^;]+);" > $null
$newConnString = $connString -replace $($matches.server -replace "\\","\\"),$value
Write-Verbose "Set-ISConnectionString newConnString:$newConnString"
if ($newConnString)
{ $connectionManager.ConnectionString = $newConnString }
}
}

} #Set-ISConnectionString

You'll notice it get the Connection Manager which matches the name of the hashtable in your example SSISConfig:
$connectionManager = $package.connections | where {$_.Name -eq "$name"}

The function then get the connection string for the connection manager:
$connString = $connectionManager.ConnectionString

And finally the code replace the server with the hashtable value in your example ".\SQL2K8" -- a dot means current server with \SQL2K8 being the instance name.

On Wed, Apr 20, 2011 at 4:00 PM, jimmycjen <notifications@codeplex.com> wrote:

From: jimmycjen

I'm trying to change the server connection inside the SSIS using Powershell, to make it simple, let's there is only one connection, and I want to change from server "ABC" to "XYZ". Is Set-ISConnectionString the right cmdlet to use ?

I was looking at the help (at http://www.sqlpsx.com/) and it gave an example as follow:

$package = Get-ISPackage 'msdb\sqlpsx2' Z002
set-ISConnectionString $package @{SSISCONFIG='.\SQL2K8'}

I can understand the 1st statement, but in the 2nd statement, I just don't understand the meaning of:

@{SSISCONFIG='.\SQL2K8'}

Can someone explain to me ? and how the 2nd statement will look like in my case ?

Thanks in advance,
Jimmy

Read the full discussion online.

To add a post to this discussion, reply to this email (SQLPSX@discussions.codeplex.com)

To start a new discussion for this project, email SQLPSX@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com


Apr 22, 2011 at 5:32 PM

Chad, thanks for the explanation.

I ran the following two statements and got the error below. Could you help ? I really appreciate it.

$package = get-ispackage -path "Msdb\RosterTextUpload" -serverName "2G-030C\SQL2008"
set-ISConnectionString $package @{"LoadingDock"=".\SQL2008"; "Membership"=".\SQL2008"; "MHPReport"=".\SQL2008"}

Exception calling "GetEnumerator" with "0" argument(s): "COM object that has been separated from its underlying RCW cannot be used."
At C:\Documents and Settings\JENJ\My Documents\WindowsPowerShell\Modules\SQLPSX\Modules\SSIS\SSIS.psm1:line:824 char:49
+     foreach ($i in $connectionInfo.GetEnumerator( <<<< ))

My SSIS package has 5 connections, 3 of them are SQL connection which I want to change the server name to ".\SQL2008". See below for more info:

Package Name: RosterTextUpload
Connection Name: LoadingDock
Connection String: Data Source=MHPDEVSRV01,2433;Initial Catalog=LoadingDock;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-RosterTextUpload-{D7CB93FE-7D46-4E41-9BCA-08868186E98D}MHPDEVSRV01.LoadingDock; .
Connection Type: SQL
Connection Name: Membership
Connection String: Data Source=MHPDEVSRV01,2433;Initial Catalog=Membership;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-RosterTextUpload-{37C0AC64-CAA2-4734-A151-E16EE83DED90}MHPDEVSRV01,2433.Membership; .
Connection Type: SQL
Connection Name: MHPReport
Connection String: Data Source=MHPDEVSRV01,2433;Initial Catalog=MHPReport;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application Name=SSIS-RosterTextUpload-{94CAA5EF-BE56-492F-BBDF-A79F912010E4}MHPDEVSRV01,2433.MHPReport;Auto Translate=False; .
Connection Type: SQL
Connection Name: RosterFile
Connection String: C:\Temp\Ports\IN\RosterFiles\SNP\sn180310.txt .
Connection Type: Flat File
Connection Name: RosterFileMCAD-FHP
Connection String: C:\Temp\Ports\IN\RosterFiles\sn180310.txt .
Connection Type: Flat File

Apr 22, 2011 at 8:33 PM

Hi Chad,

Instead of calling set-ISConnectionString (for some reason didn't work - please see my previous comment) , I coded in a different way but based on the function function Set-ISConnectionString. It worked. See below:

$package = get-ispackage -path "Msdb\RosterTextUpload" -serverName "2G-030C\SQL2008"
$connectionInfo = @{"LoadingDock"=".\SQL2008"; "Membership"=".\SQL2008"; "MHPReport"=".\SQL2008"}

foreach ($i in $connectionInfo.GetEnumerator())
{
        $name = $($i.Key); $value = $($i.Value);
        Write-Host "Key=" $name  "Value=" $value
        $connectionManager = $package.connections | where {$_.Name -eq "$name"}
       
        if ($connectionManager)
        {
            $connString = $connectionManager.ConnectionString
            Write-Host "Connection String:" $connString
            $connString -match "^Data Source=(?<server>[^;]+);" > $null
            $newConnString = $connString -replace $($matches.server -replace "\\","\\"),$value
            Write-Host "New Connection String:" $newConnString
           
            if ($newConnString)
            {
                $connectionManager.ConnectionString = $newConnString
                Write-Host "New connection has now replaced the old string."
            }
        }
       
        # Update the package with the new server name
        Set-ISPackage -package $package -path "Msdb\RosterTextUpload" -serverName "2G-030C\SQL2008" -force
}