I'm trying to create an SSIS package where failure in a certain container causes a rollback in the data flows of that container (and only that container). This package freezes, and only terminates when I stop running it or I kill the process via management studio.
I cannot post images yet so I'll give a short description.
I have two containers. The first one has:
- A data flow containing a select from a flat file into two tables which I'll call t_imp and h_imp.
- This is followed by an update task of a field ObjCde in both of those tables.
- The last data flow in the container uses t_imp as the source, further manipulating and moving the data to other tables.
The third item is where it goes wrong. I've already seen that the t_imp table is locked.
The transaction option for the package is Supported, Required for the first container and NotSupported for the second one. (The second container contains process tasks and one data flow task which should not be rolled back.) All isolation levels are on ReadCommitted.
What am I missing here?
Am I not allowed to use a table as both a source and a destination in the same container?
Do I need to set additional parameters?
