Is it possible to use synapse to copy tables from SQL server into a Dedicated SQL Pool ?

sekhemrekhutawysobekhotep 41 Reputation points
2023-03-15T16:41:09.0333333+00:00

When I try to create a synapse pipeline to copy one or more tables from an SQL server into a Dedicated SQL Pool an error happens :

Direct copying data to SQL pool using Copy command is not supported for this source type. Please enable staging.

What does the above error mean ?

I see no tutorials on this use case nor any good doc on synapse pipelines.

The big picture is I will copy a set of tables into some synapse Dedicated SQL schema. Some of these tables which will require a Transform step. Finally all tables in that schema will get copied into the Dedicated SQL Pool for downstream consumption.

Please advise

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,369 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. MartinJaffer-MSFT 26,026 Reputation points
    2023-03-16T21:02:19.3966667+00:00

    @sekhemrekhutawysobekhotep Hello and welcome to Microsoft Q&A.

    I understand you are copying tables from (on-prem?) SQL to Synapse Dedicated SQL Pool, and got a confusing error message.

    There are multiple methods for copying SQL. Copy command, Polybase, Bulk insert, and Upsert. Each has different quirks.

    User's image

    Link to doc describing Copy command details of this situation.

    Copy command works by loading data from a file on Azure Blob Storage / Azure Data Lake Gen2. As SQL is not these, Copy command wants you to enable Staging found under Copy Activity Settings.

    enter image description here

    Staging makes it so data comes from your SQL to Azure Storage, and from there gets loaded by Copy command to Dedicated Pool.

    Polybase has a similar caveat in staging.

    Bulk insert does not have such a restriction.

    As you are doing a SQL to Synapse Dedicated Pool I recommend you look at this article. Load data into Azure Synapse Analytics from Azure SQL

    As you are going to load multiple tables in bulk, I recommend you look at this article about the exact scenario. Copy multiple tables in bulk by using Azure Data Factory in the Azure portal . It says Data Factory, but it also applies to Synapse. It is a pattern so you don't have to make a pipeline / copy activity for each separate table. Instead it looks up a list of tables and generalizes.

    For transformation, you will need to use Data Flow, for almost all cases. This might not work if your source is on-prem. If yours is on-prem, not Azure hosted, you will need to first copy the data to Azure, then do Data Flow from Azure to Synapse Dedicated.

    I hope this has granted you greater understanding and provided foresight. Let me know if you have further questions.

    0 comments