You are performing the testing for, ByDesign ODATA connection issue via SQLServer Integration Service. Within SQLServer Integration Service (SSIS), you are able to create a new ODATA connection via the Connection manager and successfully test the connection. When you create a Data Flow within SSIS and create an ODATA source using the ODATA connection, system throws below error
Unable to Load Collection for the connection ByD OData Source, reason: Cannot open this connection
SQL server integration services is a product by Microsoft. Such issues were already reported by other customers, it looks like a Microsoft issue, as the same connection works nicely in other tools, or the browser.
We do not have a concrete solution for this. but below information may help:
1. Just to be sure: verify, that the password is correct. connection tests often do a technical connection test only, that is: look, if the server is reachable, but do not use the credentials to really authenticate.
Hence a typo in the username/password would not be detected by such a test.
2. It could help to enter the URL directly in a browser and provide the basic authentication credentials as queried to the browser. if something is returned, it is in fact working.
3. Some SSIS issues are discussed here: MSDN forum:
KBA , AP-RC-BCT , Business Config. Tools (SAP Business ByDesign , How To