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).