How can I make bulkCopy map the same columns?

Question

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

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:

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."

Build a column mapping of course. It's like the only other method in the bulkCopy class. [Here is how you would do it in C#](https://stackoverflow.com/questions/17469349/mapping-columns-in-a-datatable-to-a-sql-table-with-sqlbulkcopy/31909560) I am not sure how to translate it to PS for you though. Something like `$bulkCopy.ColumnMappings.Add($col1, $col2)` perhaps? — Jacob H, Jan 31 '19 at 20:46
@JacobH darn, i was hoping there was an automatic mapping option. but it appears i have to explicitly define the columns :/ — Cataster, Jan 31 '19 at 20:55
@JacobH i updated my thread with a link to an answer that I think is automatic mapping, although i am unsure of how to translate that to powershell from c# — Cataster, Jan 31 '19 at 21:09
Oh, there is automatic mapping -- it will automatically map all columns by ordinal position. Yes, this is stupid and it could handle the case of `DataTable` specifically, but it doesn't. `foreach ($column in $dmvResult.Columns) { $bulkCopy.ColumnMappings.Add($column.ColumnName, $column.ColumnName) }` should do it. (Disclaimer: untested.) — Jeroen Mostert, Jan 31 '19 at 21:28
@JeroenMostert bummer :/ well, in this case, foreach is not needed since i can just use $bulkCopy.ColumnMappings.Add($column.ColumnName, $column.ColumnName) directly. wait, by columnName, is that a property or is it something i have to fill? — Cataster, Jan 31 '19 at 21:33
Except there's no `$column` in your code anywhere, but sure, you could use multiple calls of that. The `foreach` loops through all the columns in the table. This is assuming `$dmvResult` is a `DataTable` instance. — Jeroen Mostert, Jan 31 '19 at 21:35
@JeroenMostert well $column is being selected from $dmvResult.Columns, so in this case the first iteration would be $column = [SESSION_ID], 2nd iteration result would be [SESSION_SPID]....wait a minute, this may work! — Cataster, Jan 31 '19 at 21:37
Astounding, isn't it? It's almost as if I've written code like this before. :-P — Jeroen Mostert, Jan 31 '19 at 21:38
@JeroenMostert lol, so in my case, it would be like this? foreach ($column in $dmvResult.Columns) { $bulkCopy.ColumnMappings.Add($column.ColumnName, $column.ColumnName) } $bulkCopy.WriteToServer($dmvResult) — Cataster, Jan 31 '19 at 21:39
@JeroenMostert got an exception: Exception calling "WriteToServer" with "1" argument(s): "The given ColumnMapping does not match up with any column in the source or destination." — Cataster, Jan 31 '19 at 21:40
Chuck in a `Write-Output $column.ColumnName` in the loop to debug this. The column name may not quite be what you expect, especially if (as I suspect) this is a query producing results from SSAS, which has a tendency of decorating column names. Also check that the table is correct, and that the case matches if your database uses a case-sensitive collation. Finally, keep in mind that `[name]` is just an escaped version for `name` -- the brackets are not actually part of the column name, so if your table literally has `[SESSION_SPID]` for the column name, that would be wrong. — Jeroen Mostert, Jan 31 '19 at 21:45
@JeroenMostert yes the columns are collated and everything correctly. it did copy just fine before i added this foreach loop. check the image i added for the output i get — Cataster, Jan 31 '19 at 21:50
@JeroenMostert DUDE! I have no idea how or why, but i added a comma here in the DMV_Query function " ,$dataSet.Tables[0] " and i used the foreach loop and now it works!!! how the heck is that possible?? what does that comma even do?? — Cataster, Feb 01 '19 at 01:09

score 1 · Accepted Answer · answered Feb 01 '19 at 01:11

1

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!!

answered Feb 01 '19 at 01:11

Cataster

3,081
5
32
79

This answer is confusing, because it relates to the internals of your `DMV_Query` function, which isn't posted here. However, it is posted in [this question](https://stackoverflow.com/q/54465476/45375) of yours. As for the use of `,`: `,` is the array-construction operator in PowerShell, and in the expression `, $dataSet.Tables[0]` it wraps `$dataSet.Tables[0]` in a _single-element array_, which ensures that `$dataSet.Tables[0]` - which is _itself a collection_, is output as a _single object_. – mklement0 Feb 01 '19 at 03:20
1

Without the preceding `,`, due to _PowerShell's automatic enumeration of collections_, it is the _rows_ of `$dataSet.Tables[0]` that would be output, _individually_. – mklement0 Feb 01 '19 at 03:21
1

You just saved me. Thanks!! – FranciscoNabas Jun 06 '21 at 04:19

How can I make bulkCopy map the same columns?

1 Answers1