In order to take part in a Data Transformation Services (DTS) package transaction, the data source of a connection must support distributed transactions. A step that attempts to join the package transaction with an unsupported connection, (for example, a Microsoft® Access 2000 table) fails at runtime. The following connection types, available in DTS Designer, can provide this support:
The ODBC driver must support the connection attribute SQL_ATT_ENLIST_IN_DTC and this attribute must be set. For more information, see the ODBC documentation.
Microsoft Data Link is used to access any installed OLE DB provider. An OLE DB provider must implement the ITransactionJoin interface if it is to join a distributed transaction. For more information, see the OLE DB documentation.
If your application requires tasks that access an unsupported connection, those tasks cannot join the DTS package transaction. However, failure in such a task still can be used to roll back an open transaction.