It sounds to me like the server is too busy to service the request from the SSIS Package at various times of the day, likely during peak usage or during a heavy batch process, so the login times out. Try extending the timeout period (called Connect Timeout in SSIS terms) on all your connection managers that connect to the database instance and see if that helps.