Extract transactional data from a BLOB and give each item a unique id.

[rogerzebra]

Hi All, long ago I posted something here, I'm happy to be back.

I have a question regarding blobs and how to deal with them in order to create a reporting platform and eventuelly create a BI layer of top. But, first thing first, what the firm has is a front end system that saves all the data into blobs and what I have understood is that I need to convert the blob data with a unique id for each item.

So, I would like to extract the transactional data that is saved as a blob format and then insert the data into a relational database. I don't know how to exactly go about this, I have never dealt with blobs before. But, my initial idea is to create a package in SSIS (I would assume that would work?)and then distribute the data into a stage table before the production. I would appreciate if anyone could share any experience or point me in a direction of how to go about this.
Thanks

 

[mrdenny]

Yes, you can use SSIS for this. I assume that the blob data is XML, or some other data that you can parse? You'll either need to use the native SSIS functions or a .NET script within SSIS to parse the data and return table formatted data that you can work with.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[rogerzebra]

Denny, thanks for your respond. I was able to figure it out and ssis was exellent to use for this purpose. I created an xslt style sheet and denormalized the xml data and brought it back in ssis for transformation. Worked like a charm.
Thx