Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert BLOB to TEXT Expand / Collapse
Author
Message
Posted Friday, March 14, 2008 10:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 9:06 AM
Points: 12, Visits: 38
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.
Post #469520
Posted Friday, March 14, 2008 3:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:12 PM
Points: 23,081, Visits: 31,618
What is the base type of the blob in the source database?


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #469724
Posted Friday, March 14, 2008 4:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 9:06 AM
Points: 12, Visits: 38
The column of the blob is IMAGE, but the data is text data. These are account notes stored as a blob in the db.
Post #469737
Posted Friday, March 14, 2008 4:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:12 PM
Points: 23,081, Visits: 31,618
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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #469738
Posted Friday, March 14, 2008 7:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 9:06 AM
Points: 12, Visits: 38
Error. Explicit conversion not allowed. Any other ideas?
Post #469754
Posted Friday, March 14, 2008 7:44 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:55 PM
Points: 3,129, Visits: 11,422
You might try it this way (I haven't tested this):
convert(nvarchar(max),convert(varbinary(max),MyBlobColumn))
Post #469758
Posted Friday, March 14, 2008 8:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:12 PM
Points: 23,081, Visits: 31,618
Hopefully that will work. Curious why an image field was used to hold a text blob instead of a text or ntext field.


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #469760
Posted Friday, March 14, 2008 9:07 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 8:44 AM
Points: 1,073, Visits: 6,334
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.

Post #469766
Posted Friday, March 14, 2008 9:28 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #469771
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse