SSIS communication link failure tcp provider timeout error 258

LiLiang-0529 0 Reputation points
2023-05-13T15:58:12.9733333+00:00

Hello,

I have a SSIS issue which is similar as this topic https://social.msdn.microsoft.com/Forums/en-US/94509c56-c702-4c6d-a5e2-1899a0dc68ef/shared-memory-provider-timeout-error-258?forum=sqlintegrationservices

But the answer doesn't work for me.

In my context, there is a main package SSIS to call 30 different product packages.

In each package there are about 10 to 20 stored procedures with some dependencies among them and the simultaneous running is desired for some stored proc.

After the main package running 10 or 20 minutes, the Timeout Error 258 appears for one of stored procedures (sql task with msoledbsql connector).

I have tried different configuration MaxConcurrentExecutables, Timeout, ExecuteOutOfProcess, Remote Query Timeout etc in SSIS and in SQL Server. But it is always the same result.

There are some rules I found.

  1. It's always the heaviest stored procedures exit with this error.
  2. The stored procedures on error always exit after 2 minutes (120 seconds) running and they do insert the data in the 2 minutes before the error.
  3. I limited 2 MaxConcurrentExecutables in the child package. I saw 2 stored proc running at the same and the first one finished on success but the second one ran alone untill 2 minutes on error. There isn't other process at the same time.
  4. The SQL Server trace shows SSIS 2-abort and Error 3621 - The statement has been terminated. No more information.
  5. There are the other stored proc which can run more than 2 minutes without Timeout Error.
  6. SQL Server 2019, SSISDB, MSOLEDBSQL, 200 GB memory, 12 CPU.

I realy have no idea with this error. I just want to run 300 stored procedures in a kind of parallelism.

Thank you in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,840 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,086 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 22,216 Reputation points
    2023-05-15T08:19:57.5666667+00:00

    Hi @Li Liang,

    Please check your version of SQL Server.

    If the version is SQL Server2017, you will meet the TCP Provider: Timeout error [258].

    Check: KB4499423 - FIX: Performance issues and timeouts may occur while connecting with SQL Server 2017

    The error message is a bit general, also check the blog to see if it is helpful.

    Regards，

    Zoe Hui

    If the answer is helpful, please click "Accept Answer" and upvote it.