How the transaction isolation levels in ssis works?
ssis
Solution
From what I can understand from your question you're asking what's the appropriate transaction isolation if you want to read data from a table in the same transaction that data is being written to the table? As far as I know, it shouldn't matter. The isolation types only address situations where another transaction wants to modify the same rows that the uncommitted transaction is modifying. In other words just reading the table should have no problems and you should see the data from the first Execute SQL task. Data written in a transaction is available before the transaction is committed.
For further reading, this is from the Oracle docs, but the same definition should apply to SQL and SSIS packages. Notice they address when two transactions want to modify the same data:
SERIALIZABLE: If a serializable transaction tries to execute a SQL data manipulation statement that modifies any table already modified by an uncommitted transaction, the statement fails.
READ COMMITTED: If a transaction includes SQL data manipulation statements that require row locks held by another transaction, the statement waits until the row locks are released.
Problem
I have few questions about ssis transction isolation levels. consider a scenario:I have an Execute SQL task which insert a data in a table A.This task is pointing to a dataflow task,which read the data which is previously inserted on A.I have started Distributed transaction and if i set transaction isolation in ssis as readcommited,whether it commit the table A at first execute sql task and move to dataflow task? Also what about other isolation level in this scenario?