March 14, 2008 at 10:05 am
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.
March 14, 2008 at 3:05 pm
What is the base type of the blob in the source database?
March 14, 2008 at 4:15 pm
The column of the blob is IMAGE, but the data is text data. These are account notes stored as a blob in the db.
March 14, 2008 at 4:33 pm
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.
March 14, 2008 at 7:01 pm
Error. Explicit conversion not allowed. Any other ideas?
March 14, 2008 at 7:44 pm
You might try it this way (I haven't tested this):
convert(nvarchar(max),convert(varbinary(max),MyBlobColumn))
March 14, 2008 at 8:33 pm
Hopefully that will work. Curious why an image field was used to hold a text blob instead of a text or ntext field.
March 14, 2008 at 9:07 pm
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.
March 14, 2008 at 9:28 pm
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]
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy