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 ] 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:
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.
User contributions licensed under CC BY-SA 3.0