SSIS : Multiple output rows for single input row.

Problem:

Multiple output rows for single input row.

Solution:

Step 1: Its very simple, read the file using Flat file Source




Step 2: Add a Script component, and check only those input column, which are required (to minimize the buffer size)



Step 3 : In Script component , go to the “Inputs and outputs ” section. Add new Output and give it a name e.g. “Currency”




Step 4: Add columns to new output “Currency”.



Step 5:

Write code similar to the following

' Alternative Fuel C4 Margine Amount
With CurrencyBuffer
.AddRow()
.SiteID = Row.LegacysiteID
.Currencycode = Row.CurrencyCode
.MonthId = Convert.ToInt32(Row.Month)
.YearID = Convert.ToInt32(Row.Year)
.Volumsrcode = Row.VolumeUnitofMeasureCode
.PerformanceMeasureID = 51
.FuelGradeCode = "7"
.FuelGradeDesc = Row.AlternativeFuelDesc
If Row.AlternativeFuelC4marginamt_IsNull = True Then
.Value = Nothing
Else
.Value = Convert.ToDecimal(Row.AlternativeFuelC4marginamt)
End If
MsgBox(Row.AlternativeFuelvolCurrentMth)
''Alternative Fuel Total Volume
.AddRow()
.SiteID = Row.LegacysiteID
.Currencycode = Row.CurrencyCode
.MonthId = Convert.ToInt32(Row.Month)
.YearID = Convert.ToInt32(Row.Year)
.Volumsrcode = Row.VolumeUnitofMeasureCode
.PerformanceMeasureID = 39
.FuelGradeCode = "7"
.FuelGradeDesc = Row.AlternativeFuelDesc
If Row.AlternativeFuelvolCurrentMth_IsNull = True Then
.Value = Nothing
Else
.Value = 123
End If

End With


Step 6:

While linking to the output to OLEDB Destination or any other component, choose output as Currency.





Output :