decompress gzipped BLOB with MSSQL-SP

Question

decompress gzipped BLOB with MSSQL-SP

hurr1k4ne

SSC Veteran

Points: 237
More actions
February 18, 2015 at 7:34 am

#314687

We use MSSql-Server 2008 Small Business to serve an ERP-Software.
There are text-fields containing html-styled text (WYSIWYG-Editor). These are stored inside a BLOB-field and are gzipped.
We want to copy these texts to our data-warehouse using MS-SSIS.
Wouldn't it be the best idea use a stored procedure to decompress the text and let SSIS fetch the output-string?
I've found the following for a java application doing exact this:
GZIPInputStream zin = new GZIPInputStream(blob.getBinaryStream());
DataInputStream din = new DataInputStream(zin);
Can you please help me how to add a gzip-library and to decompress a string in an SP?
Thank you very much in advance

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Wouldn't it be the best idea use a stored procedure to decompress the text and let SSIS fetch the output-string?

No, it doesn't sound like a good idea. Everything you do in the database consumes the resources of the database server, which are licensed on specs. You'd better be using the resources of the database server to do RDBMS stuff, not to decompress GZIPped blobs.

That said, SSIS can handle c# scripts in the package. See http://www.dotnetperls.com/decompress for an example of decompressing a byte array.

-- Gianluca Sartori

hurr1k4ne SSC Veteran Points: 237 More actions · Answer 2

No, it doesn't sound like a good idea

Okay,

Maybe i have missed some info.

There are about 300 item with each 3 encoded blobs.

We want to copy the text on request (not scheduled basis) from ERP-SQL into DWH-SQL.

Because they do not change very often.

I had a SP in mind with a parameter item-code and the decoded text as output.

I will have a look to do this data-manipulation in SSIS.

EDIT: Thanks for the link looks very good at first sight!

Jeff Moden SSC Guru Points: 1004666 More actions · Answer 3

I think the point that Gianluca is trying to make is why not let the app that's using the data warehouse do the unzipping on demand instead of storing the unzipped version in the data warehouse, which would make the associated tables in the DW much larger than they might need to be?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

hurr1k4ne SSC Veteran Points: 237 More actions · Answer 4

why not let the app that's using the data warehouse do the unzipping on demand

Ah, okay got ya! That a good point on storage...

Because it is a data warehouse, we have lots of applications (standard and self programmed) accessing it.

Is there a good way decompressing the texts on demand?

I think of a SP included into a view / virtual table, or again wrong idea?

Thanks!

Jeff Moden SSC Guru Points: 1004666 More actions · Answer 5

hurr1k4ne (2/20/2015)
why not let the app that's using the data warehouse do the unzipping on demand
Ah, okay got ya! That a good point on storage...
Because it is a data warehouse, we have lots of applications (standard and self programmed) accessing it.
Is there a good way decompressing the texts on demand?
I think of a SP included into a view / virtual table, or again wrong idea?
Thanks!

Are you saying that if you select a certain binary in the data warehouse that you would want to automagically unzip it and fire up the correct app on the client side (provided that the app was installed on the client side, like "Word", for example) without actually placing the data on the hard disk of the client? I'm thinking that you have to write an app to copy (zipped or unzipped) the data to the client's machine and then launch the app, kind of like what Internet Explorer allows you to do with things like PDFs, etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)