SSIS communication link failure tcp provider timeout error 258

Question

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.

Answer 1

@Kalim Arumugam
We're facing an issue with SSIS. While I can execute SQL queries in parallel in SQL Server Management Studio, SSIS is unable to run them in parallel, resulting in a timeout issue.

We tried using the 'RetainSameConnection' option, which eliminated the timeout problem, but it also removed the parallelism in SSIS, causing it to execute the stored procedures sequentially instead of in parallel for the "timeout" stored procedures.

We reported this bug to Microsoft, and their support team assisted with testing, but it was largely unproductive. Their final suggestion was to optimize our SQL, which isn't feasible and stable in our case. So we closed the support ticket at the end of last year.

As a result, the only viable solution we found on-premises was to use SQL Server jobs to run the stored procedures in parallel. This approach required creating 100 jobs and managing lots of dependencies in a table, but it was the only way to achieve parallel execution for our ETL process given the constraints.

Answer 2

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.

Answer 3

Hi @Li Liang,

I have the exact same issue; did you manage to identify the cause of your timeout problem?

@ZoeHui-MSFT any updates on this from MSFT?

Regards,

Kalim

Answer 4

Created a long post about how I managed to resolve the issue for our scenario but my post was deleted.

@Li Liang and @ZoeHui-MSFT In short, we reverted our packages to use package connection managers instead of project connection managers. This resolved the issue for us. We also have a parent-child package structure, which resulted in queries being run in parallel intermittently and ultimately leading to the issue as reported in this post.

I hope this helps others.

Regards,

Kalim