SSIS - Calculate Opening and Closing Balance

Question

I need to calculate the Opening Balance and the Closing Balance in SSIS. I have the below data as input.

invoice_date    amount
12/4/2016       4000
12/5/2016       5000
12/6/2016       7500
12/7/2016       5000
12/8/2016       8000

I want the output as below:

Opening Balance    4000
Closing Balance    8000

How can I achieve this in SSIS?

Note: Need to do using only transformations. No Execute SQL task or OLEDEB command required.

Answer 1

In my answer i will assume that your Source is an OLEDB Source and your Destination is a Flat File

You have to do the following steps:

1. Add another Dataflow Task (assuming nema = DFT Import)
2. In DFT Import Add your OLEDB Source , a Script Component and your FlatFile Destination
3. In The Script Component Mark invoice_date and amount columns as Input Columns

4. In the Script go to Inputs and Outputs Tab and make your Output Buffer asynchronous

5. Create 2 Output Columns *(Desc of type DT_STR and amount of TYPE DT_I4)

6. In your script write the following code: (Vb.net)

   Dim MinDate, MaxDate As Date
   Dim MinAmount, MaxAmount As Integer
   Dim intRowCount As Integer = 0
   Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
   
       intRowCount += 1
   
       If intRowCount = 1 Then
   
           MinDate = Row.invoicedate
           MaxDate = Row.invoicedate
           MinAmount = Row.amount
           MaxAmount = Row.amount
   
       Else
   
           If Row.invoicedate < MinDate Then
   
               MinDate = Row.invoicedate
               MinAmount = Row.amount
   
           ElseIf Row.invoicedate > MaxDate Then
   
               MaxDate = Row.invoicedate
               MaxAmount = Row.amount
   
           End If
   
       End If
   
   End Sub
   
   Public Overrides Sub PostExecute()
       MyBase.PostExecute()
   
       Output0Buffer.AddRow()
       Output0Buffer.Desc = "Opening Balance"
       Output0Buffer.amount = MinAmount
   
   
       Output0Buffer.AddRow()
       Output0Buffer.Desc = "Closing Balance"
       Output0Buffer.amount = MaxAmount
   
   End Sub
   

7. Map your output Columns to the Destination Columns

Note: if your source column datatypes are not datetime and integer you have to perform some casting method in the script

OTHER METHOD

1. Add an Execute SQL Task to get the row coubt of the source Table
2. Store the count value (Resultset) into a SSIS Variable (ex: User::intCount)

you can use a dataflow task containing an OLEDB Source and a Rowcount component instead of the first two steps and store rowcount result into a variable

3. Follow the same steps from the first method
4. In the script add User::intCount as a Readonly Variables

5. In the script write the following Code

   Dim MinDate, MaxDate As Date
   Dim MinAmount, MaxAmount As Integer
   Dim intRowCount As Integer = 0
   Dim intCurrentRow As Integer = 0
   
   Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
   
       intCurrentRow += 1
   
       If intCurrentRow = 1 Then
   
           MinDate = Row.invoicedate
           MaxDate = Row.invoicedate
           MinAmount = Row.amount
           MaxAmount = Row.amount
   
       Else
   
           If Row.invoicedate < MinDate Then
   
               MinDate = Row.invoicedate
               MinAmount = Row.amount
   
           ElseIf Row.invoicedate > MaxDate Then
   
               MaxDate = Row.invoicedate
               MaxAmount = Row.amount
   
           End If
   
           If intCurrentRow = intRowCount
   
   
           Output0Buffer.AddRow()
           Output0Buffer.Desc = "Opening Balance"
           Output0Buffer.amount = MinAmount
   
           Output0Buffer.AddRow()
           Output0Buffer.Desc = "Closing Balance"
           Output0Buffer.amount = MaxAmount
   
          End If
   
       End If
   
   End Sub
   
   Public Overrides Sub PreExecute()
       MyBase.PreExecute()
   
       IntRowCount = Variables.intCount
   
   End Sub