We have a complex SSIS package (+ sub-packages) which reads data from an Access database and updates a SQL Server (2008 R2) database.
It works OK, but I want the whole package to be transactional, so either all the records are inserted / updated or none are, so that the SQL data is kept consistent.
If I set the package TransactionOption to Required when I run the package I get a message indicating that the JET OLEDB connection cannot enlist in distributed transactions and the package fails.
Although some updates are made to the Access database, it doesn't matter if this is left in an inconsistent state as a fresh copy of the Access database is used each time the package is run.
Is there a way to make the packages run with a single distributed transaction, but to exclued the Access database connection from the transaction?
I've tried loads of options, but now I'm just going round in circles!! Thanks in advance.
Doug
It's not entirely clear what your package logic looks like, but one possible solution would be to start by copying the data 1:1 from the Access database to similarly structured tables in SQL Server. You can then continue processing using stored procedures and because all the data is already on the server, transactions are much easier to control.
This might not be possible if you have other external dependencies, but it is a fairly common ETL pattern (copy source data to a common staging database first, then transform it) so it may be worth considering for more general reasons too. But whether or not this is appropriate for you depends very heavily on your data and your system environment.