How to retain same data connection across multiple tasks in SSIS

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.

basicflow_ssis

 

 

 

 

 

 

 

 

 

 

———————————-

SQL Queries used:

 In ‘EXEC_SQL_BEGIN TRANSACTION’ execute sql task:

 BEGIN TRANSACTION

 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

ELSE

  ROLLBACK 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.

Advertisements
This entry was posted in SQL Server, SSIS. Bookmark the permalink.

12 Responses to How to retain same data connection across multiple tasks in SSIS

  1. Jerry says:

    Hi Munish,
    I would like to ask how to implement roll back if the scenario is this.

    i have to database (source and destination) and the process will be the following:
    1. transfer records from sourceDB table to destinationDB table
    2. compare if the counts transferred records in destinationDB is equal to the number of counts that are queried in #1 (since it just copy the data to destination and no deletion made in sourceDB, before the deletion checking of data integrity is made).
    3. if checking is OK, delete data in source DB.

    how can i inject transaction is i have 2 connections and will roll back automatically once error occured..

    Thanks,
    Jerry

  2. Hi Jerry,
    You could follow the below steps to fulfill your requirement:

    1. Put data flow task (DFT) in a sequence container.
    2. Select transaction option for the container as Required.
    3. Create a variable (status) of type int32 and set default value as 0.
    4. Set the value of this variable to -1 as soon as you get some kind of logical error in the DFT or at the time you come to know about the count mismatch from source to destination.
    5. Put a Scrip task in the same container after the DFT and check for the value of that variable (status) inside that. And if the value of the variable is -1, raise an error, which will fail the script task and the whole container will fail and thus the transaction would be rolled back; since we have set transaction option as Required.

    Script for raising error event in Script Task:
    Dts.Events.FireError(1001, “”, “Number of records at source & destination mismatch error !”, “”, 0)

  3. Jerry says:

    Hi Munish,

    This is a great help :). Setting up the TransactionOption property to “Required” will require MSDTC to run?

    What will be your recommendation, setting the TransactionOption property to “Required” in a component (Sequence Container, DFT etc) or to the package itself?

    Besides this approach in implementing roll back transaction, is there other way with using SSIS components and without using SP?

    Thnanks,
    Jerry

  4. Hi Jerry,
    Yes, MSDTC has to be running for that.

    You can set transaction option to Required at the package level too and can set transaction option as supported at the container then. But it will affect your other control flow tasks in the package, so take your decision looking at them as well.

    You can also achieve this by setting the property ‘ForceExecutionResult to Failure’ of the data flow task, based upon the value of the ‘status’ variable. This will fail the DFT if there is mismatch in the number of records and which in turn will roll back the transaction.
    Or
    You can delete the data at the destination after detecting the mismatch.

    Thanks,
    Munish Bansal

  5. Jerry says:

    Thank you very much Munish 🙂

  6. Vikram says:

    Hi Munish,

    we are facing a similar problem as Jerry, will ask my team to look into the details your have provided but at a high level would it matter if the ODBC connection we are using is configured to write data into a DB2 table from SQL Server 2008 ?

  7. Jerry says:

    Hi Vikram,

    With Munish suggestions and help, I already completed the package with TRANSACTION implementation. Actually I tried with two approaches:

    Approach#1: Using the TansactionOption property

    Implementation: I used Sequence Container then set the TransactionOption property of the container to Required. All components/task inside the container has the Supported value in TransactionOption property.

    Approach#2: Using Native Transaction.

    Implementation: Same as above i used a sequence container but this time the TransactionOption property is set to default value, which is Supported.

    Inside the container i have 4 Execute SQL task.

    Execute SQL Task 1 – has the script “BEGIN TRANSACTION”. Connected to Execute SQL Task 2 (green arrow)

    Execute SQL Task 2 – the script “DELETE * FROM TABLE” or any script that you want to do. Green Arrow is connected to Execute SQL Task 3 (COMMIT TRANSACTION) and another dashed red arrow is connected to Execute SQL Task 4(ROLL BACK TRANSACTION)

    Execute SQL Task 3 – has the script “COMMIT TRANSACTION”.

    Execute SQL Task 4 – has the script “ROLLBACK TRANSACTION”.

    From the connection manager make sure that the RetainSameConnection property is set to TRUE and this connection is used by all Execute SQL Task.

    Hope this will help 

  8. Thanks a lot for the detailed explaination, Jerry.

    Munish Bansal

  9. jerrybutiong says:

    Thanks also Munish.

    I learn a lot from your post…

  10. Devendra says:

    Hi Munish

    I have database which has 300 tables. i want to transfer all the data from source to destination. now the thing is i have to create 300 oledb source & destination connection which actually takes too much time.

    Does any other method available which allows me to insert data from source to destination without creating 300 oledb connection component?

  11. Eldar says:

    right click the DB->tasks->export, then go by the wizard.

  12. sairam chary says:

    Hi
    I am using the retain connection to start and close a connection.Then implementing a transaction.
    However the rollback of around 4 lac rows is taking up 4 min which I would like to optimise.
    Kindly suggest some performance tips

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s