Export SQL Server varbinary field to string using Visual Basic script in SSIS?

Question

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?

Answer 1

On line 9, instead of GetType(Byte) you need GetType(Byte()).

Encoding.GetString only takes a byte array; never a single byte, so regardless of the type provided by the framework, what's there is logically not going to work.

If there is another field in the table that comes back as a Byte, you'll need to add another branch to the If statement to handle the encoding (or convert the single byte to a byte array of length 1 and use common encoding code).