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