Problem
When working with the ZappySys SSIS Upsert Destination component, you may encounter a timeout error during data flow operations, especially when performing multiple iterations or working with large datasets. This issue often arises due to the repeated creation and disposal of connections for each iteration, which can lead to performance bottlenecks and timeouts.
e.g.
Data Flow Task Prod:Error: System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)
at Microsoft.SqlServer.Dts.Runtime.ConnectionManager.AcquireConnection(Object txn)
at ZappySys.PowerPack.Common.SsisHelper.GetDBConnectionFromSsisConnectionMgr(ConnectionManager connMgr, ISupportConnect& innerConnection)
at ZappySys.PowerPack.Common.PipelineComponentBase.GetDBConnectionFromSsisConnectionMgr(ConnectionManager connMgr, ISupportConnect& innerConnection)
at ZappySys.PowerPack.Adapter.UpsertDestination.UpsertComponentBase.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)
Cause
By default, SSIS connection managers create a new connection for each iteration of the task, even if the same connection is used multiple times. This can result in:
- Increased overhead due to frequent connection establishment and teardown.
- Timeout errors when the server takes longer to respond or if the connection limit is exceeded.
- Performance degradation during iterative operations.
Solution
To prevent timeout errors and optimize performance, you can enable the RetainSameConnection property in the SSIS connection manager. This ensures that the same connection is reused throughout the package execution, reducing overhead and preventing unnecessary disconnections.
Steps to Enable RetainSameConnection
- Open your SSIS package in SQL Server Data Tools (SSDT).
- Go to the Connection Managers pane (bottom section of the package designer).
- Right-click the relevant connection manager and select Properties.
- Locate the RetainSameConnection property.
- Set the value to TRUE.
- Save and deploy the package.
With RetainSameConnection = TRUE, SSIS will reuse the same connection for all iterations, minimizing connection-related timeouts.
Benefits of Enabling RetainSameConnection
- Reduced connection overhead: Fewer connection openings and closings improve performance.
- Faster execution: Continuous reuse of connections speeds up data flow operations.
- Fewer timeout errors: Persistent connections prevent frequent disconnections, reducing the likelihood of timeouts.
Conclusion
If you are facing timeout errors in SSIS, enabling the RetainSameConnection property is a simple yet effective solution. It optimizes connection usage, improves performance, and reduces the risk of timeout issues during iterative operations. For further assistance, please contact our support team via chat on our website.