Calculate Difference between current and previous rows in SSIS

Question

How to Calculate Difference between current and previous rows in SSIS then use that result to add a new column to the existing table

Welcome to StackOverflow! I would suggest that you add some details or an example to your question, so that people have not to guess what you are trying to do. — ventiseis, Feb 01 '17 at 23:24
To help you get started, you should read: http://stackoverflow.com/help/mcve — disappointed in SO leadership, Feb 02 '17 at 04:10

score 1 · Accepted Answer · answered Feb 01 '17 at 19:25

I'm assuming when you say "current and previous rows" is

Create 2 package variables, lets say: 'NumBefore'and 'NumAfter'. Both are Int32.
Inside the Data Flow Task, use a source component (lets say OLEDB Source) and select if its a table or a query. Lets say a table T
Drag 'Row Count' in the Data Flow Transformations list. Double click it and in the Section Variable Names, select the variable 'User::NumBefore'. Row Count task will save, in runtime, the result of the calculation in that variable.
Do whatever you want to do with the data extracted from table T. My guess is that you are going to insert new rows in the same table T, right?
You have to use a second Data Flow Task in the Control Flow. Inside drag another OLEDB Source with the same table T. Use another Row Count Task, but this time use the variable 'User::NumAfter'. After the Row Count Task use either a Script Component or a derived column.
If you use Derived Column, write a name for the column, choose the option 'Replace xxxx' if you want to replace the value of xxx column, or 'Add column' if you want to add that as a column output.
1. In expression, write: @[User::NumAfter] - @[User::NumBefore]. and the place your OLEDB Destination.

Hope this was you were looking for

1 Answers1