SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Exporting binary blob data in SSIS


Exporting binary blob data in SSIS

Author
Message
mortalic
mortalic
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 390
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!
mortalic
mortalic
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 390
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?
John Rowan
John Rowan
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5984 Visits: 4547
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
mortalic
mortalic
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 390
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! Wink
mortalic
mortalic
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 390
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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search