question

Jisan-3266 avatar image
0 Votes"
Jisan-3266 asked Jisan-3266 commented

How to connect serverless sql pool by SSIS?

I want to extract data from serverless sql pool and load into my sql server database table by visual studio SSIS package. I'm using serverless sql pool table as a source and SQL server database table as destination. I can create a OLEDB connection manager for destination. Which connection manager should I create for serverless sql pool? Which provider should I select in connection manager?

azure-synapse-analyticssql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SubashriVasudevan-1752 avatar image
0 Votes"
SubashriVasudevan-1752 answered

Hi @Jisan-3266

Thanks for posting your query on Microsoft q&a platform.

What do you want to load from server less SQL pool? Actually it is a query servicer and doesn't have tables. As per the documentation,

Serverless SQL pool has no local storage, only metadata objects are stored in databases. Therefore, T-SQL related to the following concepts isn't supported:

Tables
Triggers
Materialized views
DDL statements other than ones related to views and security
DML statements

Do you want to load from a dedicated SQL pool table instead?

Thanks

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered Jisan-3266 commented

Hi @Jisan-3266,

As @SubashriVasudevan-3801 mentioned Serverless SQL pool does not have any tables. You mean Synapse dedicated SQL pool table? If yes, its same as normal SQL server. use SQL authentication and user your dedicated SQL pool server name and database name along with SQL credentials.

You can get synapse dedicated SQL Pool server name on overview page and SQL authentication information you can get from SQL Admin User Name from properties.
206167-image.png

206231-image.png

Below link helps to understand administrative accounts in Synapse SQL.
Administrative accounts in Synapse SQL in Azure Synapse Analytics

Hope this helps. Please let us know if any further qureies.


Please consider hitting Accept Answer. Accepted answers help community as well.


image.png (245.1 KiB)
image.png (147.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello ShaikMaheer,
Thanks for you response. I was talking for serverless SQL but for dedicated SQL I'm getting same error. Please check below attached screenshot.
We can connect serverless SQL from SSMS (From version 18.5).
Azure Synapse studio screen:
206303-lakedb.png


SSMS screen:
206252-sqldb.png
SSIS conn manager view:
206148-connectionmanager.png

conn manager error:
206312-error.png


0 Votes 0 ·
lakedb.png (25.4 KiB)
sqldb.png (115.5 KiB)
error.png (69.8 KiB)