SSIS - Calculer le solde d'ouverture et de clôture
J'ai besoin de calculer le solde d'ouverture et le solde de clôture dans SSIS. J'ai les données ci-dessous en entrée.
invoice_date amount
12/4/2016 4000
12/5/2016 5000
12/6/2016 7500
12/7/2016 5000
12/8/2016 8000
Je veux la sortie comme ci-dessous:
Opening Balance 4000
Closing Balance 8000
Comment puis-je y parvenir dans SSIS ?
Remarque : À faire en utilisant uniquement des transformations. Aucune tâche d'exécution SQL ou commande OLEDEB requise.
Dans ma réponse, je supposerai que votre source est un OLEDB Source
et votre destination est unFlat File
Vous devez effectuer les étapes suivantes :
- Ajouter un autre
Dataflow Task
(en supposant que nema =DFT Import
) - Dans
DFT Import
Ajoutez votreOLEDB Source
, unScript Component
et votreFlatFile Destination
- Dans la marque de composant de script
invoice_date
et lesamount
colonnes comme colonnes d'entrée
- In the Script go to
Inputs and Outputs
Tab and make yourOutput Buffer
asynchronous
- Create 2 Output Columns *(
Desc
of typeDT_STR
andamount
of TYPEDT_I4
)
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
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
- Add an
Execute SQL Task
to get the row coubt of the source Table - 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
- Follow the same steps from the first method
- Dans le script, ajoutez en
User::intCount
tant que variables en lecture seule Dans le script, écrivez le code suivant
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