I am building out a new SQL Server 2019 database and using SSIS 2019 to import data from various sources. Most of my SSIS packages are randomly failing on the server (but not when I run them from Visual Studio), but then will run successfully, and then fail again. The failure error can change from failure to failure on the same package.
To simplify things, I built a brand new Test package that executes a stored procedure to see if I could isolate the problem. The Test package has only the one SQL task and the Connection Manager.
The Test package also randomly fails but is always for the same reason: cannot find the Connection, which it lists by the GUID. It will run several times, then fail once, sometimes twice, then run 5 or 6 times in a row, then fail. Off and on.
The packages are deployed to Integration Services Catalog and I'm calling them using Autosys to run the DTEXEC command.
Any ideas on what would cause a random failure like this?