Specify insert into list with different sql source and dest connection in Powershell

To do manual column mapping, you need to populate SqlBulkCopy.ColumnMappings. If you don't specify the mapping, then as far as I know SqlBulkCopy will assume the first column in the select list or DataRow goes into the first ordinal column of the destination table.

For example:

$bulkCopy.DestinationTableName = $DestTable;
$bulkCopy.ColumnMappings.Add('sourceColumn1','destinationColumn1');
$bulkCopy.ColumnMappings.Add('sourceColumn2','destinationColumn2');
$bulkCopy.ColumnMappings.Add('sourceColumn3','destinationColumn3');
$bulkCopy.ColumnMappings.Add('sourceColumn4','destinationColumn4');
$bulkCopy.ColumnMappings.Add('sourceColumn5','destinationColumn5');

However, there's a number of other issues with your script.

Your connection string authentication section is nonsense:

`Integrated Security=True; User ID=$UID; Password=$PWD;`

Integrated Security=True says, "Use passthrough Windows authentication with currently logged on user." User ID=$UID; Password=$PWD; says, "Use SQL authentication with the specified username and password." You can't do both. You should specify only one or the other.

$SqlCommand = New-Object system.Data.SqlClient.SqlCommand($CmdText, $SrcConn)
[...]
$bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($DestConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity) 

I may be wrong, but I'm pretty sure you're trying to pass two variables as one argument here. Just like with your ConnectionString function, I don't think you don't want parentheses here. In any case it's syntactically confusing. Do this instead:

$SqlCommand = New-Object -TypeName System.Data.SqlClient.SqlCommand -ArgumentList $CmdText, $SrcConn
[...]
$bulkCopy = New-Object -TypeName Data.SqlClient.SqlBulkCopy -ArgumentList $DestConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity

Speaking of that last one, I have another issue with it. SqlBulkCopy is powerful, but you really have to hold it's hand. By default, SqlBulkCopy doesn't run with any transaction benefits. That means that if it errors in the middle, well, too bad, your data has been partially updated. You can enable internal transactions, but then only the most recent batch of the inserts will be rolled back. You really need to manage your own transaction to get an all-or-nothing result.

So you'll end up with something like this:

Try { 
    $DestConnStr = ConnectionString $DestServer $DestDatabase

    # We have to open the connection before we can create the transaction
    $DestSqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $DestConnStr;
    $DestSqlConnection.Open();
    $DestSqlTransaction = $DestSqlConnection.BeginTransaction();
    $bulkCopy = New-Object -TypeName Data.SqlClient.SqlBulkCopy -ArgumentList $DestSqlConnection, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity, $DestSqlTransaction;
    $bulkCopy.DestinationTableName = $DestTable
    $bulkCopy.ColumnMappings.Add('sourceColumn1','destinationColumn1');
    $bulkCopy.ColumnMappings.Add('sourceColumn2','destinationColumn2');
    $bulkCopy.ColumnMappings.Add('sourceColumn3','destinationColumn3');
    $bulkCopy.ColumnMappings.Add('sourceColumn4','destinationColumn4');
    $bulkCopy.ColumnMappings.Add('sourceColumn5','destinationColumn5');

    Try {
        $bulkCopy.WriteToServer($sqlReader)
        # Commit on success
        $DestSqlTransaction.Commit();
    }
    Catch {
        # Rollback on error
        $DestSqlTransaction.Rollback();
        # Rethrow the error to the outer catch block
        throw ($_);
    }
} 
Catch [System.Exception] { 
    $ex = $_.Exception 
    Write-Host $ex.Message 
} 
Finally { 
    [...]
}

I'd probably rewrite the above more because I don't like nested try blocks, but for a quick and dirty rewrite this will work. I don't think you'll run into any problems with distributed transaction problems doing this, but I may be wrong. I tend to use SSIS or linked servers when I need this sort of data pump.