How to exclude read-only connections from DTC transactions?

Hi
I'm importing from an Access db into SQL 2005 using SSIS, and I want to execute the package inside a transaction so that I can rollback the import if I get an error. However, it seems the Access connection doesn't support DTC as I get the following error:
[Connection manager "MyConnection"] Error: The connection does not support enlisting in distributed transaction. The error goes away if I set the data flow task's TransactionOption to Supported rather than Required, but obviously I don't then get transactions.
Now, all I want to do is to read from the Access connection so I don't require it to be involved in the transaction. Is there any way I can tell SSIS this, so that it doesn't fall over? Or is there another way to achieve what I want?
On a side note - the read-only "SupportsDTCTransactions" property for the Access connection is set to True even though this is clearly not the case - would this be a bug?
[Also posted to microsoft.private.sqlserver2005.dts - I don't know which is the preferred forum - apologies]
Hi,
I got the same error when I tried to exclude from the distributed transaction a connection to an Excel source. So it seems to be a problem of Microsoft.Jet.OLEDB provider. I also inserted "OLE DB Services = -7;" in the connection string, but it didn't work.|||

Were I in your shoes, I think I'd load into a temporary table (outside of the transaction), then, inside a transaction, load the real table with an "insert into select from" Execute SQL task.

|||Yeah, I can see that would work, but it'd be fairly inefficient once the data volumes get high. I suppose with Access as a source there's not much danger of that - but I wonder how many other data sources are affected by this?
|||

There is same error in my projects. I import data from some dbf files to SQL Server 2005. And I must delete the data from Table A first, then import the data to that table. These two steps shoule be included in a transaction. But SSIS give the error like below:

Error: 0xC0014034 at Package, Connection manager "foxpro": The connection does not support enlisting in distributed transaction.

Error: 0xC0202009 at Package, Connection manager "foxpro": An OLE DB error has occurred. Error code: 0x8000FFFF.

Error: 0xC020801C at 导入CDWArea, OLE DB Source [1]: The AcquireConnection method call to the connection manager "foxpro" failed with error code 0xC0202009.

Error: 0xC004701A at 导入CDWArea, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC020801C

At the beginning, I use the conncetion driver of "Microsoft Jet 4.0", then I change it to "Visual Foxpro 9.0 ole db". But they are useless.

Anyone? Thansk!

sql