I'm using SSIS to export data from a set of tables to flat files. The export is running great except for one table with a varbinary field. The export is running a Visual Basic script task to load the data into a DataTable object, then spinning through each row and writing the values out to a flat file. Here's the loop that gets the data from the DataTable and writes it out:
For Each row As DataRow In dataTable.Rows
delim = ""
builder = New System.Text.StringBuilder
For Each col As DataColumn In dataTable.Columns
' By default, bit fields are exported as boolean (True or False).
' Cast them to int to avoid errors on import.
If col.DataType Is GetType(Boolean) And Not row.IsNull(col.ColumnName) Then
builder.Append(delim).Append(IIf(row(col.ColumnName), 1, 0))
ElseIf col.DataType Is GetType(Byte) And Not row.IsNull(col.ColumnName) Then
If isUnicode Then
builder.Append(delim).Append(New UnicodeEncoding().GetString(row(col.ColumnName)))
Else
builder.Append(delim).Append(New ASCIIEncoding().GetString(row(col.ColumnName)))
End If
Else
builder.Append(delim).Append(row(col.ColumnName))
End If
delim = delimiter
Next
writer.WriteLine(builder.ToString())
Next
When I run my SSIS package, I get this error:
System.InvalidCastException: Unable to cast object of type 'System.Byte' to type 'System.Byte[]'
I thought that Visual Basic interprets the varbinary field as a Byte array, so why am I getting a cast error?