How to Retain Same Connection Property in SSIS?

11
2679

Aim :- This article will help you to learn about How to Retain same connection property in SSIS? This question is one of the most asked interview question for SSIS. I will enlighten your brain with theory first and then we will learn how to implement this property practically. Make sense? I bet you will enjoy this ride.

Description :- In SSIS package, if we execute any task, we need to create a connection which is suitable to that particular task. The connection created may be OLEDB, Flat file, Excel, etc. These connections open up whenever that particular task needs to execute. This connection will get automatically closed when it completes its execution. So In general, we can create a separate connection for every task. But, firstly important point to know is Transaction. We all must know about transaction.

What do we mean by the term Transaction?

Transaction can be defined as a single unit of work. So, work may contain single task or multiple tasks. But it comes under one transaction only.

Sometimes when you create SSIS package, a connection scenario may arise. Suppose, Transaction start and open up in 1 connection in 1 task. After that, it may be needed to do some data manipulations in other task also. So to perform this, we have to commit or rollback this transaction in another task by using the same connection. We can achieve this by using “Retain same connection” property in SSIS. By default, this property is set to False.

Let’s start our demonstration.

Now I’m going to take 3 tasks. Out of these, 2 are “Execute SQL” tasks and 1 is Dataflow task.

  1. In 1st task i.e. “Execute SQL task”. I am going to start my transaction by writing a SQL statement like Begin Transaction.
  2. In 2nd task i.e. “Dataflow task”. I will extract the data from a text file (FlatFile) and dump that data into my local SQL Server machine.
  3. In 3rd task i.e. “Execute SQL task”, I will perform Rollback transaction.

Steps to configure Retain Same Connection property

Step1.  Create SSIS Package and Configure 1st Execute SQL Task.

  • Create SSIS package and name it as “Sample”.
  • Create OLEDB connection manager by providing yours’ Server name and Database name.
  • I am confident you all are experts at creating OLEDB connection. I’m going to use this same connection manager in my entire package.
  • Below is the screenshot of OLEDB connection manager.

Configure OLEDB Connection Manager in SSIS

  • Drag and drop “Execute SQL task”. Double click on it to configure. It will bring up a window like below.
  • You can Configure Execute SQL task by following below steps. Finally, click on OK button.
  1. Connection type = OLE DB.
  2. Connection = Give the OLEDB connection manager that we have created just before.
  3. SQL Statement = Begin Transaction.

Configure 1st Execute SQL task in SSIS

Step2.  Configure DataFlow task with FlatFile source and OLEDB destination.

  • Drag and drop Dataflow task to control flow pane. Below is the screen shot of our FlatFile source (FF).
  • Flat File as a Source
  • Drag and drop FlatFile source and double click on it to configure it. Below is the picture of source data.

Note :- This step is the center point of this article on Retain Connection Property. Here, I’m going to use the same OLEDB connection manager for “OLEDB Destination”.

Step3.  Configure 2nd Execute SQL task

  • Drag and drop “Execute SQL task” to Control flow pane.
  • Create a connection between Dataflow task and Execute SQL task. To create connection, Drag the green arrow from your Dataflow task to this Execute SQL task. Double click on it to configure.
  • Configure Execute SQL task by following the below steps. Finally, click on OK button.
  1. Connection type = OLE DB.
  2. Connection = Give the OLEDB connection manager that we have created just before.
  3. SQL Statement = Rollback Transaction.

Configure 2nd Execute SQL task in SSIS

  • Till now we have done the configuration of 3 tasks. It’s time to set the “Retain Connection Property” to “TRUE”.

Step4. Configure Retain Same Connection property to TRUE.

  • Go to connection managers and select “LH7U0CNU315BGP2\SQLEXPRESS.sample” then press F4 for properties windows. Below screen shot of properties window will clear you more on this.

Configure Retain Same Connection property in SSIS to TRUE

  • See the highlighted area in the above window, we will set the property RetainSameConnection =TRUE.
  • Finally, our SSIS package looks like below

Package flow for Retain same connection property in SSIS

  • Now you can execute the package to see whether the data get loaded into local SQL Server or not. We know it will not get loaded because we are rolling back the transaction. However we can see the data flowing from FlatFile source to Local SQL server by enabling a data viewer.

Execute package for Retain same connection in SSIS

  • By observing data viewer, we can conclude that the data is coming from FlatFile (FF) source to local SQL server.
  • Now we can move to SQL server to confirm whether this data is rolled back or not? Looking at below screen shot, we can tell that the data gets roll backed. Data was loaded from FlatFile (FF) to SQL Server machine. But, In final result it gets rolled back and our table in SQL server is empty.

Final SQL Server output

Summary :-

  1. Take one Execute SQL task to Begin the transaction.
  2. Take one DataFlow task to extract the data from FlatFile (FF) source to local SQL server.
  3. Take another Execute SQL task to roll back this transaction.
  4. Set the RetainSameConnection property to “TRUE”.
  5. Finally, we observe Expected result = Actual result in SQL server i.e. Data gets rolled back using the same Connection we created initially.

With this we complete our article on How to Retain same connection in SSIS. This is one of the most asked interview question for SSIS. I am sure you must have enjoyed our post. Please provide your valubale feedback and queries in comment below.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 8.2/10 (14 votes cast)
VN:F [1.9.22_1171]
Rating: +7 (from 9 votes)
How to Retain Same Connection Property in SSIS?, 8.2 out of 10 based on 14 ratings
  • pratap

    It was an excellent explanation.
    How to set up SMTP server in send mail task on our local machine.
    Please give reply

    • Hi Pratap,
      Welcome to PhPRing. I’m very glad to see your comment. Coming to your question,If you want to do smtp setup in your local machine. The following link might helps you. http://www.mach5.com/support/mailer/docs/smtpcfg.html. Suppose if your setting up a send mail task in your office. You need to ask your TL or friends for servername.

      Thanks,
      Avi

  • latha

    HI Avinash,
    Thanks for useful article.I have been reading a lot of articles from this website.
    Instead of using 2 execute tasks for Begin Transaction and roll back,can we make the package property “Transaction Required” enabled??

    • Hello Latha,
      Thanks for visiting our site. Very glad you liked it.
      Coming to you question, absolutely you can do it, But here the theme of the article is different. That is only the reason i’ve taken like that.

      Thanks my friend,
      Avi

  • RS

    Thanks for the explanation. Needed to understand what role RetainSameConnection played. Well explained.

    • Hi RS,

      Glad to see your comment. I bet you learned something new from this article.

      Thanks for appreciating me. Keep learning with PhpRing.

      Thanks,
      Avi

  • rakesh

    Great Tutorial brother..

  • Gangi Reddy

    Thanks a lot .it is very help full….

  • Hi Gangi Reddy,

    I’m glad you liked my post

  • upendra

    Hi,
    In my project there is a requirement to rewrite dts packages to ssis packages. I just started learning ssis packages design. It is very interesting, so i want to grow in this technology. please suggest me how to approach.

    I am thinking to learn ssis design as well as ssrs. After having some knowledge in this, am thinking of switching company. Please guide me.

    Thank you,