ColumnMappings

[jakedenyer]

Warren - was looking at this function and I noticed that the function is adding every single column to the column mappings even if you don't specify a ColumnMapping parameter. I'm actually going through some testing with this right now and I'm finding that with tables that have a large number of columns (the column number that failed in my case was 39) - the SqlBulkCopy object just doesn't like it.

In my case - what I was able to do to remediate this behavior was comment out the code in the else statement.

            if ( $PSBoundParameters.ContainsKey( 'ColumnMappings') -and        $ColumnMappings.ContainsKey($column) )
            {
                [void]$bulkCopy.ColumnMappings.Add($column,$ColumnMappings[$column])
            }
            else
            {
                #[void]$bulkCopy.ColumnMappings.Add($column,$column)
            }

With the SqlBulkCopy Object if you don't specify a Column Mapping it just goes through the Columns in order by default. This fixed the issue for me.

"If mappings are not defined—that is, the ColumnMappings collection is empty—the columns are mapped implicitly based on ordinal position. For this to work, source and target schemas must match. If they do not, an InvalidOperationException will be thrown."

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopycolumnmapping.aspx

A possible fix for the function would be a -DefaultColumnMapping switch or -NoColumnMapping (i dont know lol ) - just for special cases like this.