Raccogliendo il suggerimento di Davide Mauri, scrivo questo mio primo post a proposito dei pacchetti Integration Services di SQL Server 2005 e del loro supporto alle transazioni.

La motivazione principale che mi porta a scrivere, è che non ho trovato molto chiara la documentazione ufficiale (BOL in primis) e quindi ho dovuto fare parecchi esperimenti prima di capire come sono stati pensati e come funzionano i SSIS.

L'articolo è diviso in quattro sezioni:

1. terminologia
2. opzioni relative al supporto delle transazioni
3. avvio delle transazioni e chiusura delle transazioni (commit/rollback)
4. presentazioni di alcuni scenari, con gli elementi configurati in vari modi (sempre per quel che riguarda il supporto alle transazioni)

1. Terminologia utilizzata

Le descrizioni (a parte quella del Padre) sono prese dai Books On Line e sono riportate qui solo perché sia chiaro a cosa ci si riferisce con Pacchetti, Contenitori e Attività.

• Pacchetto
  Un Pacchetto è un insieme organizzato di connessioni, elementi di flusso di controllo, elementi di flusso di dati, gestori eventi, variabili e configurazioni.
  
  
• Contenitore
  I contenitori sono oggetti che forniscono a pacchetti e servizi la struttura necessaria per l'esecuzione delle Attività.
  Supportano la ripetizione dei flussi di controllo nei pacchetti e consentono di raggruppare Attività e contenitori in unità di lavoro significative.
  Oltre alle Attività, i contenitori possono includere anche altri contenitori.
  
  
• Attività
  Le Attività sono elementi del flusso di controllo che definiscono le unità di lavoro eseguite nel flusso di controllo di un Pacchetto.
  Un Pacchetto è costituito da una o più Attività.
  Se il Pacchetto contiene più Attività, queste ultime sono connesse e ordinate in sequenza nel flusso di controllo tramite vincoli di precedenza.
  
  
• Padre
  Si intende l'elemento che sta immediatamente sopra l'elemento a cui ci si riferisce:
  
  • un Pacchetto non ha alcun Padre (quando un Pacchetto è contenuto all'interno di un altro Pacchetto, in realtà è un'Attività 'Esegui pacchetto');
  • un Contenitore può avere come Padre un Pacchetto o un altro Contenitore;
  • un'Attività può avere come padre un Pacchetto o un Contenitore.
  
  

2. Riepilogo delle opzioni relative al supporto delle transazioni

• Required
  
  • Avvia una nuova transazione
  • Se un'Attività con opzione Required fa parte di un padre (Pacchetto o Contenitore) con opzione Required, non avvia una nuova transazione ma partecipa a quella avviata dal padre
  • Da usare quando si vuole che tutto sia eseguito in un'unica transazione:
    
    • se si imposta a livello di Pacchetto, tutti gli aggiornamenti effettuati all'interno del Pacchetto saranno parte della medesima transazione (tranne per gli elementi in cui si è impostata l'opzione Not Supported);
    • se si imposta a livello di Contenitore, tutti gli aggiornamenti effettuati dalle Attività incluse nel Contenitore saranno parte della medesima transazione;
    • se si imposta a livello di Attività e lo si specifica per ciascuna Attività, ognuna di queste Attività avvierà una sua transazione separata (a meno che non esista una transazione attiva avviata da un padre).
  
  
• Supported
  • Non avvia una nuova transazione
  • Se un'Attività con opzione Supported fa parte di un padre (Pacchetto o Contenitore) con opzione Required, l'Attività partecipa alla transazione avviata dal padre.
  • Se un'Attività con opzione Supported fa parte di un padre (Pacchetto o Contenitore) con opzione Supported, l'Attività non può partecipare alla transazione del padre, perché questi non ha avviato alcuna transazione.
  • Se un'Attività con opzione Supported fa parte di un Contenitore con opzione Supported, il quale fa parte di un Pacchetto con opzione Required, tutto ciò che ha l'opzione Supported partecipa alla transazione avviata dal Pacchetto.
  • Da usare quando si vuole partecipare ad una transazione già avviata, altrimenti a nessuna transazione.
  
  
• Not Supported
  • Non partecipa ad una transazione
  • Se un'Attività con opzione Not Supported fa parte di un padre (Pacchetto o Contenitore) con opzione Required o Supported, l'Attività non partecipa alla transazione avviata dal padre.
  
  

3. Come si avvia una transazione e quando è eseguito il Commit/Rollback

Perché sia avviata una transazione, ci deve essere un elemento (Pacchetto, Contenitore o Attività) con opzione Required, altrimenti nessuno avvia una nuova transazione.

Il Commit o il Rollback della transazione avvengono al termine dell'esecuzione dell'elemento che ha avviato la transazione:

• se è il Pacchetto ad avere l'opzione Required, il Commit/Rollback saranno eseguiti al termine dell'esecuzione del Pacchetto;
• se è il Contenitore ad avere l'opzione Required, il Commit/Rollback saranno eseguiti al termine dell'esecuzione del Contenitore;
• se è l'Attività ad avere l'opzione Required, il Commit/Rollback saranno eseguiti al termine dell'esecuzione dell'Attività;
  se sono presenti più Attività e ciascuna di esse ha l'opzione Required, il Commit/Rollback di ciascuna transazione sarà eseguito al termine dell'esecuzione dell'Attività che ha avviato la transazione.
  
  

4. Scenari

1. Scenari con il Pacchetto configurato con l'opzione Required

   Elemento	TransactionOption	Comportamento degli elementi
   Pacchetto	Required	Avvia una transazione
   Attività A	Required	Partecipa alla transazione avviata dal Pacchetto
   Attività B	Required	Partecipa alla transazione avviata dal Pacchetto
   Tutti gli aggiornamenti eseguiti dalle Attività sono eseguiti in un'unica transazione. In caso di errore in una delle Attività, sarà eseguito il Rollback di tutti gli aggiornamenti eseguiti da tutte le Attività.

   
   
   

   Elemento	TransactionOption	Comportamento degli elementi
   Pacchetto	Required	Avvia una transazione
   Attività A	Supported	Partecipa alla transazione avviata dal Pacchetto
   Attività B	Supported	Partecipa alla transazione avviata dal Pacchetto
   Tutti gli aggiornamenti eseguiti dalle Attività sono eseguiti in un'unica transazione. In caso di errore in una delle Attività, sarà eseguito il Rollback di tutti gli aggiornamenti eseguiti da tutte le Attività.

   
   
   

   Elemento	TransactionOption	Comportamento degli elementi
   Pacchetto	Required	Avvia una transazione
   Attività A	Not Supported	Non partecipa alla transazione avviata dal Pacchetto
   Attività B	Not Supported	Non partecipa alla transazione avviata dal Pacchetto
   Tutti gli aggiornamenti eseguiti dalle Attività sono eseguiti separatamente, (quanto eseguito da Attività A non influisce su Attività B). In caso di errore in una delle Attività, non sarà eseguito alcun Rollback.

   
   
   

   Elemento	TransactionOption	Comportamento degli elementi
   Pacchetto	Required	Avvia una transazione
   Attività A	Supported	Partecipa alla transazione avviata dal Pacchetto
   Attività B	Not Supported	Non partecipa alla transazione avviata dal Pacchetto
   In caso di errore di Attività A, sarà eseguito il Rollback degli aggiornamenti eseguiti dalla transazione di cui  Attività A faceva parte. In caso di errore di Attività B, non sarà eseguito alcun Rollback. Attenzione: se Attività B cerca di accedere ad una risorsa coinvolta in Attività A, risulterà bloccata dalla transazione avviata dal Pacchetto e resterà in stato “sospeso”, perché il Commit/Rollback della transazione saranno eseguiti solo al termine dell'esecuzione del Pacchetto.

   
   
   

   Elemento	TransactionOption	Comportamento degli elementi
   Pacchetto	Required	Avvia una transazione
   Attività A	Not Supported	Non partecipa alla transazione avviata dal Pacchetto
   Attività B	Supported	Partecipa alla transazione avviata dal Pacchetto
   In caso di errore di Attività A, non sarà eseguito alcun Rollback. In caso di errore di Attività B, sarà eseguito il Rollback degli aggiornamenti eseguiti dalla transazione di cui  Attività B faceva parte. Se Attività B cerca di accedere ad una risorsa coinvolta in Attività A, non ci sarà alcun blocco perché Attività A non partecipa ad una transazione.

   
   
   

2. Scenari con il Pacchetto configurato con l'opzione Supported o Not Supported

   Elemento	TransactionOption	Comportamento degli elementi
   Pacchetto	Supported / Not Supported	Non avvia una transazione
   Attività A	Required	Avvia una transazione
   Attività B	Required	Avvia una transazione
   Ogni Attività esegue i suoi aggiornamenti in una transazione separata. In caso di errore in una delle Attività, sarà eseguito il Rollback solo degli aggiornamenti eseguiti dal batch che ha generato l'errore.

   
   

   Elemento	TransactionOption	Comportamento degli elementi
   Pacchetto	Supported / Not Supported	Non avvia una transazione
   Attività A	Supported	Non partecipa ad una transazione
   Attività B	Supported	Non partecipa ad una transazione
   Non esiste alcuna transazione. In caso di errore in una delle Attività, sarà eseguito il Rollback solo degli aggiornamenti eseguiti dal batch che ha generato l'errore.

   
   

   Elemento	TransactionOption	Comportamento degli elementi
   Pacchetto	Supported / Not Supported	Non avvia una transazione
   Attività A	Not Supported	Non partecipa ad una transazione
   Attività B	Not Supported	Non partecipa ad una transazione
   Non esiste alcuna transazione. In caso di errore in una delle Attività, sarà eseguito il Rollback solo degli aggiornamenti eseguiti dall'Attività che ha generato l'errore.

   
   

   Elemento	TransactionOption	Comportamento degli elementi
   Pacchetto	Supported / Not Supported	Non avvia una transazione
   Attività A	Required	Avvia una transazione
   Attività B	Not Supported	Non partecipa ad una transazione
   In caso di errore di Attività A, sarà eseguito il Rollback degli aggiornamenti eseguiti dall'Attività A. In caso di errore di Attività B, non sarà eseguito alcun Rollback. Se Attività B cerca di accedere ad una risorsa coinvolta in Attività A, non ci sarà alcun blocco perché al termine di Attività A è eseguito il Commit/Rollback e quindi non c'è una transazione attiva che blocca Attività B.