SSIS how to split a single record in to two different records?

Question

I have a Product record that has multiple "zoned" prices, one for each store that sells the product.

ProductID int
Name      string
PriceA    money
PriceB    money
PriceC    money

In SQL Server Integration Services, I need to split this in to multiple records:

ProductID int
Version   string  // A, B, or C
Price     money   // PriceA if A, PriceB if B, etc.

This would be within a Data Flow, I presume as a Transformation between Excel source and OLE DB destination. (Assuming OLE DB is a good destination for MS SQL server).

Answer 1

If you set the "SynchronousInputID" to None under the Output, then call the AddRow() for each record output:

With Output0Buffer
    .AddRow()
    .fieldname = "some value"
End Width 
With Output0Buffer
    .AddRow()
    .fieldname = "some value"
End Width

but I still don't know how to set a value to null. Null doesn't work, and System.DBNull is a type, not a value.

Answer 2

Use an excel source, use a multicast to split into one flow for each of your values (ie PriceA, PriceB, PriceC). In the split dataflows, use a derived column to create your version value as a string type with value "A" in the first dataflow etc. Use a union all to combine the dataflows into one dataflow again and map PriceA from the first flow to an output called Price, map PriceB from the second flow to Price, and map PriceC from the third flow to Price. You now have a data flow with three times as many records as you started with.

Answer 3

The other two suggestions here are correct and will work, but there is already a built-in data flow task which can do this kind of operation without the need for manually managing the multi-cast/union or a script task. It may also be more maintainable.

This operation is usually referred to as an unpivot, since a pivot turns rows into columns and this is turning columns into rows, and there are both pivot and unpivot transformations available in the SSIS toolbox.