Error with SSIS OLE DB Connection to Azure synapse serverless pool

Question

Hello Microsoft Support Community,

I am looking to pull data from a view in a serverless SQL pool in an azure synapse workspace. To connect to and preview the data, I am using an on-prem SSIS package with an OLE DB connection manager. I am able to preview the data but am receiving this error when executing the package.

Error msg:

[OLE DB Source [2]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "

Answer 1

This error message suggests that the SSIS package is having trouble acquiring a connection from the OLE DB connection manager. There are a few things you can try to troubleshoot this issue:

1. Check that the connection manager is configured correctly with the correct server name, database name, username, and password.
2. Ensure that the serverless SQL pool is up and running and that the firewall rules are configured to allow connections from your SSIS package.
3. Verify that the user running the SSIS package has the necessary permissions to access the serverless SQL pool.
4. Check if there are any network connectivity issues or firewalls blocking the connection between the SSIS package and the serverless SQL pool.
5. Ensure that the OLE DB driver being used in the connection manager is compatible with the version of the serverless SQL pool.
6. Verify that the view you are trying to access exists and is accessible by the user running the SSIS package.

You may also want to review the SSIS package logs for more information on the error.