The Transaction Manager is not available

A few things you need to know about SSIS TransactionOption "Required".

By default, SSIS packages do not require transactions, and will not coordinate transactions. That is because the default TransactionOption is "Supported". Supported means that if this package (or subordinate object) is called from another object that has TransactionOption "Required" then it will be part of the transaction. However, if you select "Required" as the package TransactionOption (or task that consumes multiple connections, such as a sequence container) then SSIS will use DTC to manage the transactions across multiple connections. This may not be immediately intuitive to you since you might only connect to a single SQL Server. However, with multiple connections, the only way SSIS can ensure they are all committed, or none are committed, is to use DTC (Distributed Transaction Coordinator).

If the SSIS package is running on the same server as the only SQL Server database, then it is quite simple, you only need to have DTC service running. However, if the SSIS package is running on a different machine to SQL Server (this includes running SSIS in dev mode locally and attaching to another SQL machine) then you will need to ensure that DTC can communicate across machines. Otherwise, you might get a message like these

Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.

Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.".

• These error messages are, in essence, telling you that DTC cannot communicate with SQL Server. Here's what you need to check
  Ensure both computers can ping each other using netbios names. Netbios names must be used for DTC transactions.
• Both computers must have MSDTC running
• If there are firewalls on either machine, they must have exceptions for Port 135 or for the program msdtc.exe.

The dtcPing tool can be helpful in diagnosing DTC issues. For more information see http://support.microsoft.com/kb/918331

Now, whether or not you want to use large scope transactions is another question, and not a simple one to answer. A couple of points to think about when you are considering using transactions are

• Any update, insert etc to a rdmbs is either completed successfully or rolled back. Using TransactionOption "Required" as I discuss above is about combining many update/insert/delete statements so that they all succeed or none of them do.
• Be mindful of how long the transaction runs for. This is the time that another transaction might have to wait if there is a locking issue.
• There might be other ways to ensure your data integrity. For example, if you are thinking about transactions to enable simple re-running of the package in the event of an error, you might be able to structure the package so that it can re-run even after successful completion of some, or all, of the tasks.

Another point worth noting with SSIS transactions is that you can set TransactionOption of "Required" on the package, and have a task (such as Execute SQL) which has a TransactionOption of NotSupported. In this way, so long as the tasks executes, it will not be rolled back with the rest of the package in the event of a failure.

For a real-time demonstration of data that has SSIS packages loading/processing about 300 new records/second (all day, every day) see http://RichardLees.com.au/sites/Demonstrations