Blob to Text

Question

Blob to Text

Doug-363866

Say Hey Kid

Points: 674
More actions
May 19, 2009 at 6:43 pm

#131199

I have an ODBC application that stores a note file (text) as a Blob.
I need to extract that data and convert it to a flat text file.
I also need to manipulate the text afterwards, but one step at a time.
What is the most efficient way to extract the blob and convert it to text using SSIS?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246961 More actions · Answer 1

You can create a very simple data flow task that will perform the extract for you - no need to get fancy about converting anything.

But if you do need to process the text en route (eg, to remove commas or other problematic characters), this post might help you:

http://www.sqlservercentral.com/Forums/Topic713485-148-1.aspx

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Doug-363866 Say Hey Kid Points: 674 More actions · Answer 2

So if I create a data flow task and map the input blob column to an output text column, it will automatically convert it for me?

Phil Parkin SSC Guru Points: 246961 More actions · Answer 3

Your BLOB field on SQL Server will come into SSIS as data type text stream [DT_TEXT] and that is what it will remain through the pipeline, before it is output to the flat file (just remember to configure the relevant column on your flat file connection as text stream too).

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Doug-363866 Say Hey Kid Points: 674 More actions · Answer 4

Remember, my Blob is coming from 3rd party ODBC database, not from SQL Server Database.

Does this impact the data type as it comes into SSIS pipeline?

Phil Parkin SSC Guru Points: 246961 More actions · Answer 5

Not sure - try it and see what happens!

For testing purpose, I usually just do a "SELECT TOP 5 * ..." as my datasource - makes things fast.

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin