How to connect serverless sql pool by SSIS?

Jisan 1 Reputation point
2022-05-25T08:08:55.52+00:00

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 Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,253 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Suba Balaji 11,186 Reputation points
    2022-05-26T15:30:17.457+00:00

    Hi @Jisan

    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

    0 comments No comments

  2. ShaikMaheer-MSFT 37,566 Reputation points Microsoft Employee
    2022-05-27T10:24:07.2+00:00

    Hi @Jisan ,

    As @Subashri Vasudevan 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.