first of all, I am new to Integration Services. I googled everything so far, but I do not find any answers on my question. So I hope anybody of you can help me out :-).
Currently, I am migrating an T-SQL ETL process to SSIS to see the benefits in parallel executions and bulk inserts. The T-SQL codes are written sequential and persist some intermediate tables.
A simplified example would look like:
Source tables -> SQL transform -> persisted intermediate table 1 -> SQL transform -> persisted intermediate table 2 -> SQL transform -> target table.
For the proof-of-concept I do not have the time to rewrite the SQL queries and find equivalents in SSIS. Well, I do not like the idea to persist the intermediate tables in SSIS, too but I want to stream the data to the next transformation. The picture from the following link summarizes exactly what I want:
The problem: I don't know how to do it in SSIS. I haven't found any transformation step which allows SQL input and passes it to the next transformation step (OLE DB Command is not the solution as it operates on a per row basis what I have read so far).
A very dirty solution would be to put all in CTEs. I would end up with a monster SQL query what I don't like.
How do I solve my problem? I need an operator to read the target columns, take them as input, apply some SQL and send the result columns to the next operator.
Thanks for your help!