Monday, May 18, 2015

SSIS Performance Tip: RetainSameConnection Property

In today's post, I am going to talk about a connection property that will yield a better execution time in certain scenarios. A not so elusive property called RetainSameConnection. This is a property that can be set for the connection managers that will prevent them from closing after an SSIS task has completed an insert or update using that connection. Why would this be important? Well, by default, this property is set to false. Each time a package inserts rows or makes any updates to the table, the connection is opened, the inserts/updates take place, and then the connection is closed. In that kind of scenario, the default setting is fine. There wouldn't be any reason to change it. However, if multiple actions are taking place against a table, it may be a good idea to set this property to true.

For example, let's say I have a package that uses a ForEach Loop to loop through a folder, read the file names and log those file names into a table. For every iteration of the loop, the connection will be opened and then closed. In a case like that, I can set the RetainSameConnection property to true in order to save some time.

Another scenario in which this is used would be inside of packages that contain an OLEDB Command. An OLEDB Command should be avoided when possible since it is absolutely abysmal for performance. Think for a moment about what that transform actually does. It takes a row of data, opens a connection, runs a SQL statement against it, and then closes the connection. It does this for every row that is sent through it. When stuck in a situation in which you have to use it, set the RetainSameConnection property to true. That will yield a rather significant performance gain.

Where is this property? Well, inside of SSDT/BIDS, select a connection manager.



Open the properties pane by pressing F4 or locating the tab to the lower right. The property in question can be found toward the bottom.

























Open the drop down and set it to true. This should reduce the execution times of any package using a ForEach loop, or an OLEDB Command.

For more information on using this property with ForEach Loops, stay tuned for an upcoming video tutorial that I will be posting this week.


7 comments:

  1. Would be worth mentioning limitations. In certain scenarios it will trigger error "Connection is busy with results for another
    command", for example when running multiple data flows in parallel.

    ReplyDelete
    Replies
    1. Excellent! Thank you for pointing that out.

      Delete
  2. Thanks for the post!
    I have multiple containers using the same connection managers in a package that is executing in parallel, the package keeps failing randomly with the error message- 'Connection is busy for another command'.
    Will setting the RetainSameConnection property to True stop this error message? If yes, will there be any negative consequences?

    ReplyDelete
    Replies
    1. I don't think setting this property to True will help with that error. In fact, from what I have found (and also pointed out by the comment above), setting that property to true when running parallel dataflow tasks that all use the same connection manager would probably make it worse.

      Delete
  3. Apologies, I mistyped, will changing the property to 'FALSE' solve my purpose? Refer the below article:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8f1fd538-a5c8-4ae7-96fd-3a4e51100e32/how-do-i-resolve-the-error-connection-is-busy-with-results-for-another-command?forum=sqlintegrationservices

    ReplyDelete
    Replies
    1. Yes. I do believe setting it to FALSE should resolve the error. If not, then you may want to try unchecking the option to use table locks on your sources, but I would treat that as a last resort.

      Delete
    2. Sorry, meant to say destinations and not sources.

      Delete