SSIS OLE DB Destination transfer decreased between SQL to SQL DB


Short Story, after a weekend I started getting errors about some jobs IN the SQL Server Agent failing.

What I found out is that the issue is only present in X server and when I try to use a Data Flow Task, but the same package run in any other Server just fine.

I am not sure what component or service take care in a server the orquestration between the transfer of two DBs(specificaly OLE DB DESTINATION), I found some errors in the MSDTDC service, I was able to fix it but the issue persisted, now there is no more errors in the logs, but when I start transfer data via Dataflow with any type of Destination (.Net, OLE DB) sometimes the transfer fails right away and sometimes it start transfer fine very slowly decreasing the transfer to apoint that Timeout. Here is some Error logs I am getting:

[OLE DB Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Driver for SQL Server" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft OLE DB Driver for SQL Server" Hresult: 0x80004005 Description: "TCP Provider: The specified network name is no longer available. ".

So far what I have done is the following troubleshooing:

  • check both DBs, Source and Destination (Both are in perfect condition).
  • Move the package to another Server, it works just fine.
  • Fixed MSDTC service, according to the logs it stops failing.
  • Current Enviroment VS2015, Installed VS2019+SSIS package, still see the same issue.
  • removed any Windows updates that happend over the last two weeks, still same issue.
  • Try different Destination drivers, OLE DB, .Net, ODBC, still the same issue.

Side note, Funny thing is I can use Dataflow as long is no SQL to SQL, Oracle to SQL works fine, also Transfer Server Objects and transfer the Table that way would work fine as well.

Any help will be highly appreciated.


0 Answers

Nobody has answered this question yet.

User contributions licensed under CC BY-SA 3.0