SSIS Package Failing since Database failed over to Secondary (Basic Availability Group)
I figured what was the issue. Apparently the SSIS job was connecting with 2 databases that site on 2 different clusters, thus it required to connect to 2 different Listeners. When I added the user on secondary nodes of both clusters the issue was resolved. We were able to test this during the patching windows when we had to fail-over.