You can do this in SSIS (I am about to emark on a DW build that will use this principle!)

You can use the unpivot task (it is a little confusing at first but there are plenty of good examples if you google it)

Some things to consider

You need a common anchor (e.g. source record ID)

You can only unpivot columns of the same type

With strings (DT_STR) they all need to be the same length to unpivot them - Grrrrrr!

So;

Change the output lengths of the strings at the top of the stream

Multicast the stream; one for each data type

Generate an unpivot for each data type that you need.

->Passthrough the anchor values only

->unpivot the columns into with a target column of FieldName

->Leave the unpivot value as the incoming field name

Union the streams: you will have to put each datatype into a different column in the unioned stream

This will change the upstream dataset

ID,strString,intValue,dteDate

1,Bob,123,01/07/2013

2,Jim,999,31/08/2013

into

1,strString,Bob

1,intValue,,123

1,dteDate,,01/07/2013

2,strString,Jim

2,intValue,,999

2,dteDate,,,31/08/2013

You can now use lookup to translate the inbound column names into outbound column names and then use the unpivot function to rebuild the data stream.

I'm not saying it will be quick mind....