SSIS biedt ondersteuning voor transacties via de TransactionOption en IsolationLevel properties op executable niveau. Je kunt hiermee verschillende acties die je in één of zelfs meerdere SSIS packages doet als geheel doorvoeren (commit) óf terugdraaien (rollback). Hoe je de TransactionOption en IsolationLevel properties kunt gebruiken en wat de verschillende opties betekenen, wordt goed beschreven in de online documentatie en in diverse SSIS boeken. Maar wat er nu precies onder water gebeurt, wordt slecht beschreven.
Als je vanuit SSIS een transactie initiëert middels de TransactionOption property, maakt SSIS gebruik van de Microsoft Distributed Transaction Coordinator (MS DTC), een standaard Windows component. Dat merk je heel snel wanneer je MS DTC stopt op de server waarop SSIS draait. Je krijgt dan bij het uitvoeren van het package een foutmelding:
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.
MS DTC moet dus draaien op dezelfde server als waarop de SSIS service draait (en op je ontwikkel PC, wanneer je een package test vanuit Business Intelligence Development Studio). MS DTC beheert alle transacties die SSIS initieert. Het voordeel daarvan is dat wanneer bij de transactie meerdere toepassingen betrokken zijn de transactie toch als geheel terug te draaien is. Zelfs wanneer een transactie betrekking heeft op acties op een SQL Server en een Oracle database op verschillende servers, kan deze teruggedraaid worden.
Er zijn een aantal hulpmiddelen om te zien dat er DTC transacties actief zijn. Eén daarvan is Component Services (onder Administrative Tools) die een node bevat voor DTC.
Zoals gezegd, beheert MS DTC alle transacties die je in SSIS intitieert. DTC geeft daartoe een ID terug voor de transactie. Voor alle connecties die binnen de transactie gebruikt worden (feitelijk bepaald door de verschillende taken en de bijbehorende connection managers die je binnen de transactie uitvoert), wordt vervolgens bepaald of deze DTC transacties ondersteunen. Als dat zo is, wordt de connectie opgezet als onderdeel van de lopende transactie (enlist). Gaat de transactie goed, dan wordt deze via DTC op connectie niveau gecommit. Op dezelfde manier wordt de transactie teruggedraaid op alle connecties wanneer de transactie ergens fout gaat.
Niet alles wat je binnen een SSIS transactie doet, kan teruggedraaid worden. Alleen toepassingen die zelf al transacties ondersteunen én MS DTC ondersteunen, kunnen gemanaged worden door DTC. Dat is het geval bij de meeste databases en bijv. message queues (zoals MSMQ). Verwijder je binnen een SSIS package waarvoor je TransactionOption op Required hebt gezet een bestand met een File System Task of doe je iets op een FTP server via de FTP Task, werkt dat niet onder de door DTC beheerde gedistribueerde transactie!
In het geval van SQL Server connecties, kun je binnen SQL Server ook zien dat er DTC transacties actief zijn. Je kunt daarvoor de stored procedure sp_lock gebruiken. Door DTC gelockte objecten herken je aan de process ID –2.
Merk overigens op dat één van de beperkingen van DTC transacties is dat altijd een hele tabel gelocked wordt tijdens de transactie. Wil je tijdens het debuggen toch even kijken wat er in een tabel zou staan wanneer de transactie gecommit wordt, gebruik dan de WITH (NOLOCK) hint.
Ook met SQL Server Profiler kun je zien of er DTC transacties gestart worden door het event DTCTransaction te tracen:
De kracht van DTC transacties zoals Integration Services die gebruikt zit hem in de letter D: distributed. Wanneer een transactie meerdere toepassingen, mogelijk op meerdere servers en zelfs databases van verschillende leveranciers omvat, zorgt DTC ervoor dat dit ook werkt zoals gewenst. Duik je dieper in DTC, dan lees je ook heel wat problemen met het mechanisme. Zoals eigenlijk altijd wanneer er meerdere servers en netwerkverkeer daartussen in het spel zijn. Zo staat default op de Security tab van de DTC Properties alle netwerk toegang voor DTC uit. Als transacties die je binnen SSIS initieert uitsluitend op de SSIS server “leven”, geen probleem. In de praktijk zul je hier meestal nog wel wat configuratie moeten doen. Ook firewalls kunnen gedistribueerde transacties hinderen. Loop je in de praktijk tegen dit soort scenarios aan, is het goed te weten dat er een hulpmiddels als DTCPing waarmee je de communicatie tussen de gedistribueerde servers die een rol spelen in een transactie kunt testen.