Ostatnio zetknąłem się z problemem dotyczącym wykorzystania transakcji natywnych SQL Servera z poziomu pakietu SQL Server Integration Services (SSIS). Abstrahując od tego, czy jestem zwolennikiem zaszywania kodu T-SQL w pakietach SSIS, czy nie, problem wydał mi się dobrym materiałem na wpis na blogu :-)

Wyobraźmy sobie uproszczony pakiet, który wygląda tak:

image

  • SQL Begin Tran 1: zadanie Execute SQL Task na połączeniu Connection Manager 1 (połączenie z SQL Server, właściwość RetainSameConnection=True) z kodem T-SQL: BEGIN TRAN;.
  • SQL Begin Tran 2: zadanie Execute SQL Task na połączeniu Connection Manager 2 (połączenie z SQL Server, właściwość RetainSameConnection=True) z kodem T-SQL: BEGIN TRAN;.
  • SQC Do Something: sekwencja czynności do wykonania, nieistotne, na którym połączeniu. Ważne: w jednym z kroków sekwencji ustawiana jest wartość pewnej zmiennej w paczce SSIS.
  • SQL Commit Tran 2: zadanie Execute SQL Task na połączeniu Connection Manager 2 z kodem T-SQL: COMMIT TRAN;.
  • SQL Commit Tran 1: zadanie Execute SQL Task na połączeniu Connection Manager 1 (właściwość RetainSameConnection=True) z kodem T-SQL: IF ? = 1 COMMIT TRAN; ELSE ROLLBACK; (pod ? podstawiamy zmienną z paczki SSIS, której wartość była ustawiana w sekwencji).

Oczywiście, kody specjalnie upraszczam, ale istota problemu została zachowana – mamy dwie transakcje, na różnych połączeniach, jedna ma się toczyć niezależnie od drugiej, ale druga ma zostać zatwierdzona (COMMIT), tylko, gdy pierwsza została zakończona powodzeniem, czego wyrazem ma być wartość zmiennej w paczce SSIS ustawiona na 1.

Problem? Ostatni krok kończy się błędem z komunikatem:

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

Dlaczego tak się dzieje? Aby odpowiedzieć na to pytanie, dobrze jest – wykorzystując aplikację SQL Server Profiler – przechwycić kod T-SQL wykonywany przez pakiet.

Okazuje się, że kod T-SQL na połączeniu Connection Manager 1 wygląda na kształt:

BEGIN TRAN;
-- tu coś robimy...
EXEC sp_executesql N'IF @zmienna = 1 COMMIT TRAN; ELSE ROLLBACK;',
                   N'@zmienna int',
                   @zmienna = 1;

Problem wynika zatem z faktu, że gdy kod T-SQL musi być parametryzowany, SSIS uczciwie wykorzystuje parametryzację za pomocą procedury systemowej sp_executesql (“bezpieczny” dynamiczny T-SQL). A kod wewnątrz sp_executesql zachowuje się tak, jak procedura składowana – krzyczy na złego człowieka, jeśli wartość @@TRANCOUNT na starcie nie jest równa wartości @@TRANCOUNT na końcu wykonywanego kodu (w konkretnym przypadku na dzień dobry była otwarta transakcja, a na końcu transakcji nie było – została zatwierdzona).

Wniosek: jeśli już koniecznie transakcje SQL-owe tworzone poprzez zaszywanie kodu T-SQL rozproszonego po zadaniach w pakietach SSIS, to nie mieszajmy wysyłania wsadów (nieparametryzowanych kawałków kodu T-SQL) z parametryzowanymi blokami kodu wykonywanymi za pomocą sp_executesql. Inaczej efekt może być taki, jak powyżej.