计算开闭平衡。

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

我需要在SSIS中计算开盘价和收盘价。我有以下数据作为输入。

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

Can someone help me out achieving this in SSIS?

有人能在SSIS帮我实现这个目标吗?

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

注意:只需要使用转换。不需要执行SQL任务或OLEDEB命令。

1 个解决方案

#1

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

在我的回答中，我假设您的源是一个OLEDB源，而您的目标是一个平面文件

You have to do the following steps:

你必须做以下步骤:

1. Add another Dataflow Task (assuming nema = DFT Import)
2. 添加另一个Dataflow任务(假设nema = DFT导入)
3. In DFT Import Add your OLEDB Source , a Script Component and your FlatFile Destination
4. 在DFT导入中，添加OLEDB源文件、脚本组件和FlatFile目的地
5. In The Script Component Mark invoice_date and amount columns as Input Columns
6. 在脚本组件中，将invoice_date和amount列标记为输入列

4. In the Script go to Inputs and Outputs Tab and make your Output Buffer asynchronous
5. 在脚本中，转到input和Output选项卡，并使您的输出缓冲区异步

5. Create 2 Output Columns *(Desc of type DT_STR and amount of TYPE DT_I4)
6. 创建2列*(类型为DT_STR的Desc和类型为DT_I4的数量)

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

   在脚本中，编写以下代码:(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

注意:如果源列数据类型不是datetime和integer类型，那么必须在脚本中执行一些强制转换方法

OTHER METHOD

其他方法

1. Add an Execute SQL Task to get the row coubt of the source Table
2. 添加执行SQL任务以获取源表的行库
3. Store the count value (Resultset) into a SSIS Variable (ex: User::intCount)
4. 将计数值(Resultset)存储到SSIS变量中(例如: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

您可以使用包含OLEDB源和Rowcount组件的dataflow任务，而不是前两个步骤，并将Rowcount结果存储到一个变量中

3. Follow the same steps from the first method
4. 遵循第一个方法的相同步骤
5. In the script add User::intCount as a Readonly Variables
6. 在脚本中添加User:::intCount作为只读变量

7. 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