SSIS Flat File Unpivot

Question

Hello,

I have a rather complex Flat File that I'm trying to parse using SSIS. The overall structure of the flat file is as follows:

CustomerName, CustomerAddress, CustomerContactInfo, InvoiceDate1, InvoiceAmount1, InvoiceDate2, InvoiceAmount2, InvoiceDate3, InvoiceAmount3

I'm trying to load this into two tables in my database, a Customer table and an Invoice table. CustomerName, CustomerAddress, and CustomerContactInfo all need to be inserted into the customer table. InvoiceDate1 and InvoiceAmount1 need to be a row in the Invoice table with a foreign key to the Customer table. Same for InvoiceDate2 and InvoiceDate3.

I tried using just the unpivot transformation, but it wants to unpivot the Customer too, and I can't do that. Is there a good way to handle this in SSIS?

Answer 1

In your Data Flow, you can use the Multicast component to send a the data down multiple paths. After the Source component add the Multicast component. From one Multicast output send the data to the Customer table. You can ignore the unused columns in any subsequent component.

In the second output from the Multicast, attach the Unpivot transformation. Have it ignore all columns that you don't want to include in the Invoice table. Then, it will process only the column that will be added to the table.