'Resolving 'Received an invalid column length from the bcp client for colid' with PowerShell

I ran into this error, "Received an invalid column length from the bcp client for colid" while trying to do a bulk copy to SQL, using PowerShell:

$bulkCopy = New-Object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.BatchSize = 1000
$bulkCopy.BulkCopyTimeout = 0
$bulkCopy.DestinationTableName = "[dbo].[tablename]"
$bulkCopy.WriteToServer($dataTable)

I found the top solution at Received an invalid column length from the bcp client for colid 6 and would like to try to convert it to PowerShell. The code from the solution:

try
{
    bulkCopy.WriteToServer(importTable);
    sqlTran.Commit();
}    
catch (SqlException ex)
{
    if (ex.Message.Contains("Received an invalid column length from the bcp client for colid"))
    {
        string pattern = @"\d+";
        Match match = Regex.Match(ex.Message.ToString(), pattern);
        var index = Convert.ToInt32(match.Value) -1;

        FieldInfo fi = typeof(SqlBulkCopy).GetField("_sortedColumnMappings", BindingFlags.NonPublic | BindingFlags.Instance);
        var sortedColumns = fi.GetValue(bulkCopy);
        var items = (Object[])sortedColumns.GetType().GetField("_items", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(sortedColumns);

        FieldInfo itemdata = items[index].GetType().GetField("_metadata", BindingFlags.NonPublic | BindingFlags.Instance);
        var metadata = itemdata.GetValue(items[index]);

        var column = metadata.GetType().GetField("column", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);
        var length = metadata.GetType().GetField("length", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);
        throw new DataFormatException(String.Format("Column: {0} contains data with a length greater than: {1}", column, length));
    }

    throw;
}

I hit a roadblock at (and presumably after):

FieldInfo fi = typeof(SqlBulkCopy).GetField("_sortedColumnMappings", BindingFlags.NonPublic | BindingFlags.Instance);

Using Get-Member on my $bulkcopy object, I see that I have BatchSize, BulkCopyTimeout, ColumnMappings, DestinationTableName, EnableStreaming, and NotifyAfter properties, but ColmnMappings is empty.

How would I achieve this result in PowerShell?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source