Forum Discussion

Element115's avatar
Element115
Memorable Member
2 years ago

QUESTION::COPY DATA::UPSERT FAIL

The source is an on-prem SQL Server, and the destination is a lakehouse table.  I use a Copy data activity pointing to the lakehouse SQL endpoint in order to get access to the Upsert option.   Ca...
data engineering
frithjof_v's avatar
frithjof_v
Community Champion
2 years ago

My guess is that the error message you are seeing ("edition of SQL Server"), is talking about the destination (Lakehouse SQL Analytics Endpoint) and not about the source (on-prem SQL Server). 

 

It seems you are trying to write to the Lakehouse SQL Analytics Endpoint. Isn't the Lakehouse SQL Analytics Endpoint read-only? 

 

What happens if you try to use a Data Warehouse table as destination instead of a Lakehouse SQL Analytics Endpoint table?

 

It seems to me you are trying to use the Azure SQL Database connector for the destination (because the Azure SQL Database connector has the Upsert option), but you are providing the connection string to a Lakehouse SQL Analytics Endpoint as the destination. Is this what you are wanting to do (i.e. upsert data into a Lakehouse table)? 

 

I am not sure if that is supposed to work in Fabric Data Pipeline
(same for using a Data Warehouse connection string with Azure SQL Database connector, I am not sure if that is supposed to work also).

 

It can be interesting to experiment and test and see what happens. Although I don't know if it is supported.

 

 

I guess if you want to do upsert into a Fabric Lakehouse table, you can use Notebook (shown in this exercise https://microsoftlearning.github.io/mslearn-fabric/Instructions/Labs/03b-medallion-lakehouse.html)

  • Element115's avatar
    Element115
    Memorable Member
    2 years ago

    That's a good question.  Since the SQL endpoint is available in the dropdown list for Connection in the Destination tab, I don't see any reason why not use it.  Now, if it is read-only even from a Copy data activity process, then the dev team responsible for the product should remove the SQL endpoint as a Connection choice from the list, because as long as it is there, the implicit message is that we can use it for Insert or Upsert (according to the GUI)!

     

    It seems to me you are trying to use the Azure SQL Database connector for the destination (because the Azure SQL Database connector has the Upsert option), but you are providing the connection string to a Lakehouse SQL Analytics Endpoint as the destination. Is this what you are wanting to do (i.e. upsert data into a Lakehouse table)?

     Correct, yes.

     

    I am not sure if that is supposed to work in Fabric Data Pipeline
(same for using a Data Warehouse connection string with Azure SQL Database connector, I am not sure if that is supposed to work also).

    Well, Microsoft made it available. Therefore I assume it means it could be used.  Unless, ofc, it's a bug. 😁