September 8, 2009 at 12:47 pm
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!
September 8, 2009 at 4:25 pm
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?
September 8, 2009 at 4:27 pm
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.
September 8, 2009 at 4:51 pm
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:
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! 😉
September 10, 2009 at 11:56 am
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...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply