Get Calculated Value From Previous Row With OLE DB Command

Published in

Akkim Akademi

3 min readAug 12, 2022

Sometimes, I can’t perform an operation in SQL. Because, It is don’t allow this. We can get help from SSIS or ETL tools in this regard. It is a ETL tools;

OLE DB Command

The OLE DB Command transformation is used to run an SQL statement for each row of its input data flow to insert, update or delete records in a database table. The transform has one input, one output and one error output. Generally speaking, its input data flow is always defined as arguments to feed the parameters defined in the SQL statement of the transform.

Example

Let’s say you can’t calculate the previous row. Also, the row to be calculated from now on will calculate according to the value calculated before it.

For this reason, the LAG() function is not suitable for this.
For this reason, we may decide to use OLE DB Command for the loop.

Step 1:

Let’s say you have a table like the one below. In this table, the unit cost is calculated after the weighted cost is calculated. After each row is calculated, the value in the lower row takes the calculated value in the upper row and calculate it.

Now by adding a task, we add the source table before adding the OLE DB Command step.

Step 2:

Then, before entering the loop, we assign the parameters to be assigned to the source file as columns.

Step 3:

Then we enter the OLE DB Command section and add the links and the code we want to be repeated.

UPDATE [Cost_Simulation].[dbo].[Cost_Rows] SET [WEIGTED_COST]=(CASE WHEN [UNIT_COST] IS NOT NULL THEN (((SELECT [WEIGTED_COST] from [Cost_Simulation].[dbo].[Cost_Rows] SM WHERE STOCK_ID=? AND [ID]=(?-1)) *
(SELECT [Kümülatif] from [Cost_Simulation].[dbo].[Cost_Rows] SM WHERE STOCK_ID=? AND [ID]=(?-1)))+
([AMOUNT_ENTER]*[UNIT_COST]))/Cumulative
WHEN [UNIT_COST] IS NULL THEN ((SELECT [WEIGTED_COST] from [Cost_Simulation].[dbo].[Cost_Rows] SM WHERE STOCK_ID=? AND [ID]=(?-1)))END) WHERE ID=?
UPDATE [Cost_Simulation].[dbo].[Cost_Rows] SET [UNIT_COST]=(CASE WHEN [UNIT_COST] IS NULL THEN (SELECT [WEIGTED_COST] from [Cost_Simulation].[dbo].[Cost_Rows] SM WHERE STOCK_ID=? AND [ID]=(?-1))
ELSE [UNIT_COST END) WHERE ID=?

Then we execute the data flow. I wanted it to run up to the 12th ID to run fast. According to him, I gave a where condition in the source section. (ID<13)

After the process is completed, the calculation will be made according to the range you have given.