This happened a few days ago, a data flow from a flat file source to a SQL table failed when it encountered a different data type in a column, but to my surprise, it didn't roll back the transaction. Several rows were left inserted into the table. Is this normal behavior? Does SSIS split up a data flow into multiple transactions? The row count for this dataflow is about 230000..
IsolationLevel is set to Serializable, TransactionOption is supported.
I found this comment: The IsolationLevel property in SSIS components only applies when distributed transactions are used (package or other container has TransactionOption=Required). To me that means you have to set TransactionOption=Required for every package, otherwise you can't control what's happening in a data flow?