Update For Anyone Interested
I have spent the last couple of days trying to chase theories of memory problems and/or network problems. I would be happy to share the memory research if anyone wants, but my Network Administrator and I are thinking that memory problems are not the issue in this case. So, in this post, I wanted to share what I found regarding network activity in case this will spark an idea for someone.
We turned on Windows "Resource Monitor" program on the server, called lets say MyServer, which is running the SQL Server instance in question. I tested running the SSIS package from two places on MyServer: 1) Visual Studio 2017 (SSDT) and 2) SQL Server, with package loaded into MSDB.
- When running the SSIS package from Visual Studio on MyServer, the Resource Monitor shows that the "image" of "DtsDebugHost.exe" is what is running. CPU ranges from about 50 to 80+ percent. The "send" column shows about 6,000 to 7,000 bits per second. The "receive" column shows 700,000 bits per second, give or take. These numbers are fairly consistent until the job finishes running about 3 to 4 minutes after starting.
- When running the SSIS package as a scheduled Agent job on SQL Server (which is on the same MyServer as test #1 above), the Resource Manager show thats the "image" which runs is "DTExec.exe", not the DtsDebugHost.exe. I think that is what we would expect, right? CPU ranges from about 80 to 90+ percent. In the first few seconds, the "send" column shows about 8,00o bits per second. The "Receive" column shows about 350,000 bits per second, give or take. It is during this time that a few small tables *do* successfully download from the outside Sybase database into our local database.
---> Then after a few seconds, the DTExec.exe image still shows that CPU is at about 94%, but the image has completely *disappeared* from the Network box. i.e.: There is just no network activity at all for the SSIS package while CPU usage remains ridiculously high.
--->I didn't keep my eyes glued to the Resource Monitor the whole time after that, but I left the SSIS/Agent job running while I checked in with the Resource Monitor periodically. What I would see sometimes is that DTExec.exe would appear in the Network box again. I never saw the same volume that I would see in the first few seconds of the job running. If DTExec.exe made an appearance again, I would see "send" values of often 0/zero (which makes sense if my query already went to Sybase and now it is just just waiting for data to come back) and "receive" values were maybe 8,000 to 12,000 bits per second. After about 50 minutes, another table finished downloading. After another hour after that, I stopped the job and no other tables had finished downloading.
What does this mean???
My Network Administrator thinks all of the above information means that this is not a network problem. That it is a problem with the software within SQL Server. Does that sound right to you? Do you think this information is a clue to what may be going on or is this irrelevant information? Does anyone have any thoughts on what I should try to look into next?
I can try looking into the SSIS Catalog, but if that isn't going to change how SSIS runs, I'd rather focus on a solution to this problem right now. I took a quick look at the above link. I think it is going to be helpful when I switch to using the Catalog, but nothing jumped out at me that it would affect how any package runs.