Required:
Indicates that the container starts a transaction, unless one is
already started by its parent container.
If a transaction already exists, the container joins the
transaction. Supported:
Indicates that the container does not
start a transaction, but joins any transaction started by its parent container.
If no
transaction is started by its parent container, it is not bound by any
transaction.NotSupported:
Indicates that the container does not start a transaction or join
an existing transaction.
A
transaction started by a parent container does not affect child containers that
have been configured to not support transactions.
Practical Example info:
The
package contains a FOREACH LOOP CONTAINER.
It
iterates through the files in the folder and the complete file name with the
path is stored in the variable “var_Filename”.
The
FOREACH LOOP CONTAINER contains a DATA FLOW TASK.
It
has one FLAT FILE SOURCE and one OLEDB DESTINATION.
FLAT
FILE SOURCE connection string is configured with the variable “var_Filename”.
So
in every loop of the Foreachloop container the FLAT FILE SOURCE points to a new
file in the folder.
It
iterates through 3 “.txt” files present in a folder.
Files
are ff1_TPinForeach.txt, ff2_TPinForeach.txt
and ff3_TPinForeach.txt.
File
“ff1_TPinForeach.txt”:
Column1
1
2
3
File
“ff2_TPinForeach.txt”:
Column1
4
5
6
File
“ff3_TPinForeach.txt”:
Column1
7
8
A
The
data from these files are transferred to the table “TPinForeach” which contains
only one column “Column1” of data type NUMERIC.
**
The last row of File “ff3_TPinForeach.txt” has invalid record so as to trigger
the failure of package.
Scenario 1:
Transaction
Option of FOREACH LOOP CONTAINER = Supported
Transaction
Option of DATA FLOW TASK = Supported
On
executing the package,
-
Package
fails
-
The
table contains 8 records from 1-8
Reason:
Since
there was no component having Transaction Option = REQUIRED, so no transaction
was started at any point during the package execution.
As
a result NO Rollback happened while the data was transferred from the files to
the table.
Thus
the table contains 8 records as the failure happened in the 9th
record.
Scenario 2:
Transaction
Option of FOREACH LOOP CONTAINER = Supported
Transaction
Option of DATA FLOW TASK = Required
On
executing the package,
-
Package
fails
-
The
table contains 6 records from 1-6
Reason:
Since
the Transaction Option = REQUIRED for DATA FLOW TASK, so a new transaction was
started when the execution of DFT started. The transaction terminated once the
execution of the same DFT was finished.
While
transferring data from the file “ff3_TPinForeach.txt” into the table, the DFT
failed.
Since
it was part of a transaction, the data transferred in that transaction is Rolled
Back.
Thus
the table contains 6 records and not the 7th and 8th
record. These 7th and 8th record were part of the
transaction which contained Wrong 9th record. As a Rollback
functionality the 7th and 8th were rolled back.
Scenario 3:
Transaction
Option of FOREACH LOOP CONTAINER = Required
Transaction
Option of DATA FLOW TASK = Supported
On
executing the package,
-
Package
fails
-
The
table contains 0 records
Reason:
Since
the Transaction Option = REQUIRED for FOREACH LOOP CONTAINER, so a new
transaction was started when the execution of FOREACH LOOP CONTAINER started.
The
DATA FLOW TASK has Transaction Option = Supported. So it joined the above
transaction and became part of the same transaction.
Thus
when the FOREACH LOOP CONTAINER failed because of the error 9th
record, the transaction started by the FOREACH LOOP CONTAINER Rolled back.
Since the DFT were part of the same transaction they rolled back completely
from the beginning i.e. from record 1.
Scenario 4:
Transaction
Option of FOREACH LOOP CONTAINER = Required
Transaction
Option of DATA FLOW TASK = Required
On
executing the package,
-
Package
fails
-
The
table contains 0 records
Reason:
Same
as Scenario 3