Le Transazioni in SSIS

Di Alessandro Alpi - Microsoft MVP - SQL Server

In questo tip vedremo come SSIS permette di gestire le transazioni al suo interno.

Come sappiamo una transazione è una sequenza di operazioni eseguite in un'unica unità logica di lavoro. Questa unità deve soddisfare quattro criteri, le cosiddette proprietà ACID (atomicità, consistenza, isolamento e durata). Sono fondamentali per mantenere la consistenza e l'integrità dei dati.

Da MSDN:

Atomicità

Una transazione deve essere un'unità di lavoro atomica, ovvero devono essere eseguite tutte le modifiche dei dati oppure non ne deve essere eseguita nessuna.

Consistenza

Al termine della transazione i dati devono essere consistenti. Per salvaguardare l'integrità dei dati in un database relazionale, è necessario che alle modifiche eseguite dalla transazione vengano applicate tutte le regole. Al termine della transazione tutte le strutture di dati interne, ad esempio gli indici b-tree o gli elenchi collegati doppiamente, devono risultare corrette.

Isolamento

Le modifiche eseguite da transazioni simultanee devono essere isolate dalle modifiche eseguite da qualsiasi altra transazione simultanea. Una transazione riconosce lo stato dei dati precedente alla modifica eseguita da una transazione simultanea oppure lo stato successivo al completamento della seconda transazione, ma non è in grado di riconoscere stati intermedi. Questa proprietà viene detta serializzabilità in quanto consente di ricaricare i dati iniziali e di eseguire nuovamente una serie di transazioni in modo da ripristinare lo stato dei dati successivo all'esecuzione delle transazioni originali.

Durata

Gli effetti di una transazione completata sono permanenti all'interno del sistema. Le modifiche eseguite rimangono valide anche in caso di errore del sistema.

Anche SSIS supporta l'utilizzo di transazioni e tutti i contenitori (Cicli For, For each, Sequence container e Host container di ogni task non container) danno la possibilità di gestirle come meglio si crede. Ma vediamo come.

I contenitori di cui parlavo sono i seguenti:

Inoltre ogni task è contenuto in un host container invisibile a chi realizza il SSIS ed impostato quando vengono impostate le proprietà dell’attività contenuta (immagine sopra)

Anche lo stage del control flow e del data flow, sono contenitori e in quanto tali, supportano l’utilizzo di transazioni.

Detto questo, la proprietà che ci interessa per la gestione delle transazioni è la TransactionOption:

Può assumere tre valori Supported, NotSupported, Required.

Required

Il contenitore avvia una transazione, a meno che il contenitore padre non abbia già avviato un'altra transazione. In tal caso il figlio parteciperà alla transazione già in corso nel container padre.

Immaginiamo di avere un pacchetto con un For Each container su cui è impostata l'opzione Required. Se il pacchetto non lancia una transazione, il For Each avvierà la propria. Se per il pacchetto è stata impostata l'opzione Required, il For Each parteciperà a quella del package.

Supported

Il contenitore non avvia una transazione, ma partecipa alla transazione avviata dal contenitore padre.

A titolo di esempio pensiamo ad un package che avvia una transazione. All’interno del pacchetto pensiamo ad un paio di task Execute SQL con l’opzione Supported. Se uno dei task fallisce viene eseguita una ROLLBACK di tutti e due i task, poiché entrambi partecipano alla transazione avviata dal package padre.

Se al contrario il pacchetto non avvia una transazione e per i task è selezionata l’opzione Supported, gli Execute SQL non partecipano ad alcuna transazione, ma ognuno di essi ne avvia una. In questo modo, se si hanno errori su di un task, verrà eseguita una ROLLBACK solo delle modifiche effettuate dal task stesso.

NotSupported

Il contenitore non avvia una transazione e non partecipa ad una transazione in corso.

Ad esempio, pensiamo ad un package contenente un For Each container. Il package avvia una transazione il container non supporta le transazioni. In questo caso, se nel For Each container si verifica un errore sui task in esso contenuti, nessuna rollback viene eseguita.

Conclusioni

Con l’utilizzo delle transazioni in SSIS è molto più semplice mantenere la consistenza e l’integrità dei dati. Nella maggior parte dei casi è fondamentale rendere le operazioni ACID (atomicità, consistenza, isolamento e durata) al fine di avere una base dati sempre robusta e corretta sotto tutti i punti di vista.

In ogni caso è sempre buona norma mantenere le dimensioni delle transazioni piuttosto piccole, anche per non perdere punti per quanto riguarda le prestazioni del pacchetto.

Fatene buon uso..