Convert BLOB to TEXT

Question

Post reply

Convert BLOB to TEXT

Doug-363866

Say Hey Kid

Points: 674
More actions
March 14, 2008 at 10:05 am

#188042

I have a source DB that contains a BLOB which is really a bunch of text. I am trying to use SSIS to read this BLOB and stream the chars as text to a flat file which will later be FTP'ed to another server and reloaded as nvarchar records.
In my data flow task I have my source (SQL DB BLOB) and target (Flat File) of course...in between I am using a script component to try and convert the BLOB. Here is my code:
Dim intBlobLength As Integer = Convert.ToInt32(Row.denoteblob.Length)
Dim intFinish As Integer = intBlobLength - 1
Dim byteBlob(intFinish) As Byte
byteBlob = Row.denoteblob.GetBlobData(0, intFinish)
Row.outdenoteline. = byteBlob
Row.outdebtorrowid = Row.debtorrowid
I get a pipeline is to small error. I cant seem to adjust the size of the outdenoteline within the script componenet.
please help!
Regards.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

What is the base type of the blob in the source database?

Doug-363866 Say Hey Kid Points: 674 More actions · Answer 2

The column of the blob is IMAGE, but the data is text data. These are account notes stored as a blob in the db.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Try building a view over this table. In the select statement for the view, do this for the image field:

cast(image_field as nvarchar(max))

And then use the view as your data source for the transfer to a file.

😎

Doug-363866 Say Hey Kid Points: 674 More actions · Answer 4

Error. Explicit conversion not allowed. Any other ideas?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

You might try it this way (I haven't tested this):

convert(nvarchar(max),convert(varbinary(max),MyBlobColumn))

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Hopefully that will work. Curious why an image field was used to hold a text blob instead of a text or ntext field.

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 7

How about changing this line?

Row.outdenoteline. = byteBlob

to

Row.outdenoteline.AddBlobData(byteBlob)

I ran your code against a small table with a few 1MB IMAGE columns and they all made it to the Flat File destination without error. The output rows type was DT_TEXT.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Doug Graham (3/14/2008)
Error. Explicit conversion not allowed. Any other ideas?

Sounds like you've got bad data. One or more of your image fields may not be all text.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]