SSIS Script component – Get Blob Data column

Recently I have to move varbinary(max) data from one database to another database using script component.

When dealing with varbinary(max), there are two scenarios:

• the length of the data is moderate
• the length of the data is big

GetBytes() is intended for the this scenario, when we are using CommandBehaviour.SequentialAccess to ensure that we are streaming the data, not buffering it. In particular, in this usage we would usually be writing (for example) in a stream, in a loop. For example:

// moderately sized buffer; 8040 is a SQL Server page, note
byte[] buffer = new byte[8040]; 
long offset = 0;
int read;
while((read = reader.GetBytes(col, offset, buffer, 0, buffer.Length)) > 0) {
    offset += read;
    destination.Write(buffer, 0, read); // push downstream
}

However! If we are using moderately sized data, then use this code:

byte[] data = (byte[])reader[col];

Obviously the output column data type would be “image [DT_IMAGE]” in script component.

Finally to make it part of output;

OutputBuffer.FileData.AddBlobData((byte[])sqlReader["FileData"]);

Sources

https://stackoverflow.com/questions/11135245/what-length-parameter-should-i-pass-to-sqldatareader-getbytes

Add to favorites