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

Exporting binary blob data in SSIS Expand / Collapse
Author
Message
Posted Tuesday, September 8, 2009 12:47 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:37 PM
Points: 56, Visits: 293
Here's my issue, I have a lot of small files in a medium sized database that are binary blobs.
Their filenames and work order numbers are stored in seperate columns, and it seemed pretty cut and dry to me, use SSIS, Create a derived column to split off the filename and then an export column to export the binary data to disk. It works (mostly)! except the binary data is exported as garbage. For example filename.xls won't open in excel, and when opened in a text editor is basically garbage that looks like this:
begin 660 so80236.xls
MT,a1X*&Q&N$`````````````````````/@`#`/[_"0`&```

It goes on for pages of course.
from what I can see there are .jpg's and .txt files as well as lot's of other stuff in here too.

Also, I am getting this error over and over again:
Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 4 buffers were considered and 4 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
I increased my paging file to 5.1gb then monitored it during a run and it never came close to using it so I'm confused as to why it's throwing this error.

Rest of the stats:
4gb ram 64-bit server 2003 with sql server 2005 (64-bit)

Thanks!
Post #784493
Posted Tuesday, September 8, 2009 4:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:37 PM
Points: 56, Visits: 293
I think I have figured out what the problem is, the column called FILEBLOB is of type ntext and when exported, exports as ntext. Is there a way to change this? or is this a case of the vendor db choices have torpedo's my chances from the start?
Post #784621
Posted Tuesday, September 8, 2009 4:27 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: Wednesday, November 12, 2014 6:29 PM
Points: 3,840, Visits: 3,852
I have not seen this before, but it probably has to do with your destination adapters. If you want to create an Excel workbook, you need to use the Excel destination, if you want a flat file, you'll use the flat file destination and configure it for csv, fixed, etc.

You could use the conditional split to read the file name and route specific files to their matching destination types.




John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #784623
Posted Tuesday, September 8, 2009 4:51 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:37 PM
Points: 56, Visits: 293
Thanks for your reply, this is a little different I think than dealing with excel outputs, these are actual binary files that from what I can tell the vendor has stored in the database as blobs, but the datatype is ntext in sql server and unicode text stream [DT_NTEXT] in ssis.
Take a look here for the process I followed to attempt this export:
http://books.google.com/books?id=i4gg8AqZHMoC&pg=PA159&lpg=PA159&dq=exporting+blobs+sql+server+2005&source=bl&ots=B0ialdUUgp&sig=z2OlNuOE-Mnlo9XiOyIayNdJ3gQ&hl=en&ei=E4OhSpbQC4WesgOtiv2MDw&sa=X&oi=book_result&ct=result&resnum=9#v=onepage&q=&f=false

An interesting twist I just found out, if you change the datatype on the external column for the OLE DB SOURCE to a [DT_BYTES] (varbinary datatype) the export column transformation doesn't see it as a blob any longer.

I'm so confused about how to procede with this my head hurts! ;)

Post #784634
Posted Thursday, September 10, 2009 11:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:37 PM
Points: 56, Visits: 293
Well for anyone that's curious, I figured out what was going on. The vendor had uuencoded the binary data and inserted it into the ntext field. yeah...
Post #785867
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse