Post reply

SSIS - meaning of RetainSameConnection property of DB connection

  • January 16, 2014 at 5:59 pm

    #281252

    In database connection managers such as .NET and OLEDB, there is a property called retain same connection. This can be set to true or false. An example of why you need to set it to true (step 6) - http://microsoft-ssis.blogspot.com/2011/09/ssis-transactions-with-tsql.html

    Please tell me what is the meaning of RetainSameConnection and how it can affect your package. Also, do we have a C# equivalent of that, like maybe myDBConnection.RetainSameConn = true ?

    Also, if we see this property in a config file, does true = -1 ? See below -

    <Configuration ConfiguredType="Property" Path="\Package.Connections[MyDBNetConn].Properties[RetainSameConnection]" ValueType="Boolean">

    <ConfiguredValue>-1</ConfiguredValue>

    </Configuration>

    Thanks.

  • January 17, 2014 at 12:00 am

    #1680969

    You can see a connection manager as a factory creating connections.

    Each time a connection manager is used by an SSIS component, a new connection is created.

    If you set RetainSameConnection to true, you tell the connection manager to create only one connection and hold on to it as long as the package runs. This prevents temporary tables or transaction to be dropped.

    Regarding the value in a configuration: you can easily check this by creating a configuration through the package configurations editor. Set the property to true, create the configuration and check what SSIS generated.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 17, 2014 at 11:05 am

    #1681184

    Koen Verbeeck (1/17/2014)

    You can see a connection manager as a factory creating connections.

    Each time a connection manager is used by an SSIS component, a new connection is created.

    If you set RetainSameConnection to true, you tell the connection manager to create only one connection and hold on to it as long as the package runs. This prevents temporary tables or transaction to be dropped.

    Regarding the value in a configuration: you can easily check this by creating a configuration through the package configurations editor. Set the property to true, create the configuration and check what SSIS generated.

    Thanks. Great explanation ! So its like the singleton pattern in object oriented programming ?

  • January 19, 2014 at 5:51 am

    #1681390

    blasto_max (1/17/2014)

    Koen Verbeeck (1/17/2014)

    You can see a connection manager as a factory creating connections.

    Each time a connection manager is used by an SSIS component, a new connection is created.

    If you set RetainSameConnection to true, you tell the connection manager to create only one connection and hold on to it as long as the package runs. This prevents temporary tables or transaction to be dropped.

    Regarding the value in a configuration: you can easily check this by creating a configuration through the package configurations editor. Set the property to true, create the configuration and check what SSIS generated.

    Thanks. Great explanation ! So its like the singleton pattern in object oriented programming ?

    Possibly. It's been a very long time since I had a look at design patterns.

    I thought there was also a "factory" pattern.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply