0

Our SSIS package randomly fails with the following error message:

An exception has occurred during data insertion, the message returned from the provider is: WriteToServer requires an open and available Connection. The connection's current state is closed.

Every time it's a different step, sometimes the package runs w/o any issue for a few days in a row, but then suddenly fails. There were no changes introduced in the package itself nor to SQL Server configuration. The only thing which might change is basically the amount of data, it gradually increases. We are running Integration Services in SQL Server 2016 (it's installed on the same machine along with SQL Server). There is nothing suspicious on the SQL Server side, when the SSIS failure occurs the other apps are connecting to SQL Server with no issues, the SQL error log is empty.

Has anyone spotted anything similar? Any tips on how to troubleshoot this? I am not a big fan or expert in SSIS :) so I really count on your expertise!

1
  • In all my years of dealing with SSIS, I don't believe I've encountered this error. Can you describe what your SSIS package is doing, the type of connections (ado or ole), the specific component that is raising the exception and anything else that might be going on here?
    – billinkc
    Aug 3 '20 at 20:24
0

In my experience I have only removed the package and republished it ...

1
  • thanks a ton @calabresefrj I will give a try!
    – GrzegorzO
    Aug 3 '20 at 14:13
0

Do you have any reports running out of hours WITH (NOLOCK).

NOLOCK query hint locks schemas, and whilst it does not interfere with reads, it can deadlock on update and deletion tasks and can cause time out errors.

This may explain why your connection is dropping when you on the same server.

Easiest way to identify if this is the case, log in and monitor for deadlocking during the SSIS package run using SP_WHO2 and other job related queries, paying particular attention to BLK columns.

But if a report is running slow, this may be what is happening.

Once deadlocked, both resources will wait until the other is finished or times out, it's a war of attrition that the shorter time out will lose.

Alternatively, conflicting writes might be interfering with your package.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.