Thursday, January 9, 2014

SSIS Package Design Solution from Normalized tables to De-normalized Data Mart Layer / SSIS Columns to Rows

I had a problem while building one package.
I had to read the columns
Contact Association Contact Type Device Type Value
Business Telephone Cellphone 12312
Business Telephone landline 1231231
Business Telephone Fax 1342343
Home Telephone Cellphone 12312
Home Telephone landline 1231231
Home Telephone Fax 1342343

This has to be inserted in 

Some Junk Columns Business Cell No Business Tele No Bus Fax Home Cell No Home Tele No Home Fax 
Junkies.. 12312 1231231 1342343 12312 1231231 1342343
like this, there are 92 columns in my warehouse table.
I was little pissed off with the approach suggested in my design document.
Do lookup for business entities and store bla bla.. then Pivot..

Pivot is not only meant for transposing columns to rows. it was best while aggregating / any operation how you play in your excel.

here is my design.
Do the lookups and get all data in one set.
Distribute the required set of rows with Conditional split Component
Add a Derived Columns component for every conditional split flow, Rename the columns accordingly,

I could not provide my solution samples since it would not be in compliance. will try to mock the scenario and some dummy package and post later.