Can this kind of logic can be implemented in SSIS and is it possible to do it in near-real time?
Users are submitting tables with hundreds of thousands of records and waiting for the results for up to 1 hour with the current implementation when the starting table have about 500.000 rows (after the STEP1 and STEP2 we have millions of records). In the future the amount of data and the user base may drastically grow.
ID_A, ID_B, COEF
In my personal experience that kind of logic can be completely implemented in SSIS.
I would do it in a Script Task or Component for two reasons:
There are, though, some professionals that have a bad opinion of "complex" scripts and...
The down step of this approach is that you need some ability with .NET and programming, also most of your package logic will be focus there and script debugging can be more complex than other components. But once you get to use the .NET features of SSIS, there is no turning back.
Usually getting near real time in SSIS is tricky for big data sets, and sometimes you need to integrate other tools (e.g. StreamInsight) to achieve it.