Estou tentando usar transações explícitas no SSIS. O banco de dados com o qual estou trabalhando não tem o DTC ativado, então não posso usar o tratamento de transação embutido do SSIS.
Eu executei tarefas sql para inicializar, confirmar e reverter a transação, no entanto, apesar de ter definido "RetainSameConnection" como verdadeiro, ainda estou recebendo o erro abaixo:
[Execute SQL Task] Erro: Executando a consulta "ROLLBACK;" falhou com o seguinte erro: "A solicitação ROLLBACK TRANSACTION não tem BEGIN TRANSACTION correspondente.". Possíveis motivos de falha: Problemas com a consulta, propriedade "ResultSet" não definida corretamente, parâmetros não definidos corretamente ou conexão não estabelecida corretamente.
Acontece que eu tinha feito algo realmente estúpido.
Meu pacote SSIS, ao que parece, era perfeito, eu havia nomeado as transações e definido "RetainSameConnection" como verdadeiro. Os comandos Begin/commit/rollback foram todos especificados corretamente.
Meu problema era que um dos procedimentos armazenados chamados como parte do pacote SSIS continha uma transação sem nome, fazendo com que o SQL Server confirmasse tudo assim que o commit desse procedimento armazenado fosse chamado.
A resolução foi modificar o procedimento armazenado para nomear a transação, para que o SQL Server trate essa transação como uma transação aninhada (em teoria, eu teria pensado que a segunda instrução "begin transaction" faria isso, mas por algum motivo isso não estava acontecendo ).