In SSIS for each task; we use; in control flow pane of a package, we need to specify some connection to execute that task. This may be an OLEDB connection, a File connection etc and the tasks using these connections may be an ‘Execute SQL task’, a ‘File System task’ or a ‘Data Flow task’.
Now as we as have to provide connection for each & every task separately, it means for each task a new/separate connection is opened every time. This connection gets closed after the completion of that particular task. This is useful as in a SSIS package; we are dealing with distributed data sources or data servers.
But sometimes we may require to have a transaction opened in one connection in one task & after doing some operation we have to either commit or rollback that transaction using the same connection but in a later task. This can be achieved using “RetainSameConnection” property of the connection manager defined.
You might be thinking of the distributed transaction support available in SSIS to have the same functionality, but let me clarify that available distributed transactions like ‘supported’ etc comes into role if we want to either commit or rollback whole set of transaction based on success or failure. This cannot be controlled based on some logical conditions. But here by retaining the same connection across all the tasks participating in a transaction, we can achieve this.
Let’s take an example:
We will begin a transaction using a ‘Execute SQL task’, then will perform some operations using a DFT. In that DFT we will set a variable ‘SomeCondition’ to either 0 or 1, based upon some condition. And then by looking at the value of this variable we will either Commit or Rollback that transaction; using another ‘Execute SQL task’.
But Before that we need to create an OLEDB connection to use it across all the tasks.
Set RetainSameConnection to TRUE.
SQL Queries used:
In ‘EXEC_SQL_BEGIN TRANSACTION’ execute sql task:
INSERT INTO CheckRetainConnection
VALUES(100, ‘John M’)
In ‘EXEC_SQL_COMMIT_ ROLLBACK’ execute sql task:
Use expressions tab to set the SQLStatementSource for the below query:
“IF 1=”+ (DT_STR, 5, 1252) @[User::SomeCondition] +
“ COMMIT TRANSACTION
So if we set variable SomeCondition as 1, insertion performed in first execute sql task will be committed else it would be rolled back.