Search code examples
sql-serverpowershelldmv

How can I make bulkCopy map the same columns?

Suppose I have a SQL Table that has these columns:

[server_name],[SESSION_ID],[SESSION_SPID]

I am trying to copy values stored in a data table ($dmvResult) to the SQL Table above ($Table)

$dmvResult = DMV_Query 'SELECT [SESSION_ID]
      ,[SESSION_SPID]
FROM $SYSTEM.DISCOVER_SESSIONS';

$ConnectionString ='Data Source=$server; Database=$database; Trusted_Connection=True;'
$bulkCopy = new-object Data.SqlClient.SqlBulkCopy($ConnectionString)
$bulkCopy.DestinationTableName=$Table
$bulkCopy.WriteToServer($dmvResult)

While the copying is being done successfully, there is an issue: it's copying by position, not by column name match. In other words, the copied columns are not being mapped and copied to the same columns.

[SESSION_ID] is being copied to [server_name] and [SESSION_SPID] is being copied to [SESSION_ID]

How can I tell bulkCopy to match columns and copy?

The result copy should be [server_name] being empty because it wasn't selected from DMV query.

I found a neat solution in this thread:

https://stackoverflow.com/a/20045505/8397835

but I dont know how to translate it to my powershell code:

var meta = definition.Context.Mapping.GetMetaType(typeof(T));
        foreach (var col in meta.DataMembers)
        {
            copy.ColumnMappings.Add(col.Member.Name, col.MappedName);
        }

EDIT: foreach column.ColumnName output foreachoutput

EDIT2: i tried this:

$dmvResult.Columns |%{$_.Name}

and it doesnt output anything.

before you say $dmvResult data table must be empty then, explain how is it possible that this actually works and copies in data?

$bulkCopy.ColumnMappings.Add('SESSION_ID', 'SESSION_ID')
$bulkCopy.ColumnMappings.Add('SESSION_SPID', 'SESSION_SPID')
$bulkCopy.WriteToServer($dmvResult)

and for some reason, its outputting this to the console as well:

consoleoutput

so the data table $dmvResult is clearly populated.

i was hoping instead of defining mapping for every single column like this:

$bulkCopy.ColumnMappings.Add('SESSION_SPID', 'SESSION_SPID')

instead there would be anutomatic option like this:

foreach ($column in $dmvResult.Columns) { $bulkCopy.ColumnMappings.Add($column.ColumnName, $column.ColumnName)}

but that throws exception:

Exception calling "WriteToServer" with "1" argument(s): "The given ColumnMapping does not match up with any column in the source or destination."

Solution

  • A very weird solution but I just had to add a comma here before $dataset:

    ,$dataSet.Tables[0]

    in the DMV_Query function

    and then i used this foreach loop

    foreach ($column in $dmvResult.Columns) { $bulkCopy.ColumnMappings.Add($column.ColumnName, $column.ColumnName) > $null }

    and it worked!

    it now maps the columns automatically!!