Ok, for whatever reason we compress the XML Blobs we store in the database.  This created the problem for me to view the data, as the DBA is always asked to go find this record in the XML.

I ended up creating a little windows form application in VS2005, using C# that can decompress the blobs so I can view the data.

I also added a peice that shows how to compress the blobs and save them to a database.  There was also a fun peice about displaying byte[] data in datagridview, it's in there!

I've also created a sample database with one table and a few records that are compressed, it's a sql server backup file, or you'd have to modify the code to create a table with some compressed blobs to see it work.

This was a just a fun thing to do, we get a lot of mileage out of compressing the XML data.

BlobTest.bak (2.58 MB)
XMLBlobViewer.ZIP (64.2 KB)

Screen shot:

This post may raise more questions for you than it answers, but it really is something that happenned in my shop and confused some people.  So I'm creating this post as a place holder so I'll be able to reference this 10 months from now when the same issue comes up and I've forgetten the solution.

Basically for whatever reason we use Excel, darn !  No matter how I try and recreate these Excel sheets in Reporting Services, some business users won't bite and they still need excel files.  Generally these SSIS to Excel data pumps work fine....the ones that generally trip the process up are ones with long text and multiple "named" work sheets.  To solve the "named" work sheets problem we often use an Excel template file that is copied to the destination and renamed, than the data pump occurs.  As we migrate the SSIS packages from Development, QA to production, sometimes an "odd" excel worksheet gets introduced, usually named "sheet1", lovely, below is the procedure we used to correct this...

Thanks to Tom Reeves from our team.

To Delete Unwanted sheets in Excel for use in SSIS

1) Map a local drive to the E: drive of the app server (ServerNameHere\E$)
2) Open template file on server and delete unwanted tabs
3) Open SSIS package
a. You’ll need to make note of the tab name(s) that you are working with
b. Copy query from the source object in the data flow
4) Create a new DTS package – SQL2000
a. 1 SQL Connection
b. 1 Excel Connection
c. 2 Execute SQL Tasks
i. Both pointed to the Excel connection manager
ii. One for Drop table and one for Create table
5) Open the Transformation flow between the SQL connection and the Excel connection - DTS
6) Copy and paste the query from the SSIS package into the Source tab
7) Click the Create button on the Destination tab and copy the code from the window.  You can hit cancel on this screen now.
8) Open one of the Execute SQL Tasks and set the connection manager to the Excel connection and type your Drop sql statement. 
a. Example: drop table `SheetNameHere` - No dollar sign in the table name
8) Open the other Execute SQL Task and set the connection manager to the Excel connection and type your Create sql statement.
Example:
CREATE TABLE `SheetNameHere` (`Division_Code` Decimal (2,0) ,
`Division_Dept_Id` Decimal (7,0) ,
`Office_Dept_Id` Decimal (7,0) ,
`Last_Update_Date` DateTime ,
`Reason_Text_Opt_out` LongText ,
`Reason_Text_Opt_in` LongText ,
`WHQ_Comments` LongText ,
`WHQ_User` VarChar (30) ,
`WHQ_Last_Update_Date` DateTime ,
`military` Short ,
`office_type_code` VarChar (1) ,
`ABC` VarChar (10) ,
`Client_Segmentation` VarChar (50) ,
`number_of_windows` Long ,
`number_of_desks` Long ,
`Number_of_bilingual_tax_pros` VarChar (50) ,
`Year_round_indicator` VarChar (1) ,
`Latino_designation_year_prior` Long ,
`office_status` VarChar (1) ,
`Notary` Long ,
`ITIN` Long ,
`Spanish_Speaking_Preparer` Long  )

What's for Dinner 8/2 - 8/8

Sunday

• Smoked Brisket
• Fried Country Potatoes
• Green Beans

Monday

• Hamburgers and Hotdogs
• Baked Beans
• Green Beans
• Salad
• Sliced Tomatoes

Tuesday

• Spaghetti and Ravioli
• Meatballs
• Peas
• Salad
• Sliced Tomatoes

Wednesday

• Smoked Pork Country Ribs
• Mashed Potatoes
• Carrots
• Peas

Thursday

• Leftovers

Friday

• Pizza