ssis merge join more than 2 data sets

Question

I'm working on an ssis package to fix some data from a table. The table looks something like this:

CustID FieldID INT_VAL DEC_VAL VARCHAR_VAL DATE_VAL
1      1       23
1      2               500.0
1      3                       David
1      4                                    4/1/05
1      5       52369871
2      1       25
2      2               896.23  
2      3                       Allan
2      4                                    9/20/03
2      5       52369872

I want to transform it into this:

CustID FirstName AccountNumber Age JoinDate Balance
1      David     52369871      23  4/1/05   500.0
2      Allan     52369872      25  9/20/03  896.23

Currently, I've got my SSIS package set up to pull in the data from the source table, does a conditional split on the field id, then generates a derived column on each split. The part I'm stuck on is joining the data back together. I want to join the data back together on the CustId.

However, the join merge only allows you to join 2 datasets, in the end I will need to join about 30 data sets. Is there a good way to do that without having to have a bunch of merge joins?

Answer 1

That seems a bit awkward, why not just do it in a query?

select
    CustID,
    max(case when FieldID = 3 then VARCHAR_VAL else null end) as 'FirstName',
    max(case when FieldID = 5 then INT_VAL else null end) as 'AccountNumber',
    max(case when FieldID = 1 then INT_VAL else null end) as 'Age',
    max(case when FieldID = 4 then DATE_VAL else null end) as 'JoinDate',
    max(case when FieldID = 2 then DEC_VAL else null end) as 'Balance'
from
    dbo.StagingTable
group by
    CustID

If your source system is MSSQL, then you can use that query from SSIS or even create a view in the source database (if you're allowed to). If not, then copy the data directly to a staging table in MSSQL and query it from there.