[connectors-sdk] `BaseConnectorSettings` support for connectors as module

[Powlinett]

__main__.__file__ is not always available at module import time, but is at runtime. So, my main contribution was to do the tests to confirm that the connectors-sdk can work with the connectors developed as modules.

Proposed changes

• extract the logic to locate config files from _SettingsLoader.settings_customise_sources to their own methods
• raise meaningful error if __main__.__file__ is not available at runtime (for very rare cases)
• update unit tests to really read from filesystem (not mocking files' content anymore)

Related issues

• #5295

Checklist

• [x] I consider the submitted work as finished
• [x] I have signed my commits using GPG key.
• [x] I tested the code for its functionality using different use cases
• [ ] I added/update the relevant documentation (either on github or on notion)
• [x] Where necessary I refactored code to improve the overall quality