Copy activity into Lakehouse with schemas enabled

[SimonDalecky]

Hi, I am trying to understand, if I do anything wrong, or there is a actuall issue/bug in Copy Activity in Fabric.

I am trying to copy a table where source is on-premise SQL Server 2019 and a target destination is a Fabric Lakehouse with enabled schemas (preview feature).

Expected behaviour: In the target lakehouse and schema will get created new managed table based on name specified in copy activity.

Actual behaviour: Schema argument is ignored by copy activity. It creates a schema based on table name and no managed table gets created.

Image of copy activity setup

Copy activity json

{
    "source": {
        "type": "SqlServerSource",
        "queryTimeout": "02:00:00",
        "partitionOption": "None",
        "datasetSettings": {
            "annotations": [],
            "type": "SqlServerTable",
            "schema": [],
            "typeProperties": {
                "schema": "XXXX",
                "table": "XXXX",
                "database": "XXXX"
            },
            "externalReferences": {
                "connection": "XXXX"
            }
        }
    },
    "sink": {
        "type": "LakehouseTableSink",
        "tableActionOption": "Append",
        "partitionOption": "None",
        "datasetSettings": {
            "annotations": [],
            "linkedService": {
                "name": "flhBronzeDWHgeneralSimon",
                "properties": {
                    "annotations": [],
                    "type": "Lakehouse",
                    "typeProperties": {
                        "workspaceId": "XXXX",
                        "artifactId": "XXXX",
                        "rootFolder": "Tables"
                    }
                }
            },
            "type": "LakehouseTable",
            "schema": [],
            "typeProperties": {
                "schema": "dbo",
                "table": "TestTable"
            }
        }
    },
    "enableStaging": false,
    "translator": {
        "type": "TabularTranslator",
        "typeConversion": true,
        "typeConversionSettings": {
            "allowDataTruncation": true,
            "treatBooleanAsNumber": false
        }
    }
}

And here what gets created inside the Lakehouse

Many thanks for any assitance. 

[frithjof_v · Accepted solution]

@SimonDalecky I tried this today and it seems to be working now.

Ref. Re: Load data to different schema in Fabric Lakeho... - Microsoft Fabric Community

I created these schemas and tables by using Copy Activity in Data Pipeline:

Perhaps it helps to only use lowercase letters in the schema name.

[tkiwi]

Hey, do you know where your capacity is located? It's not working in our fabric 😞

[DarioMB]

I am working on a UK South Fabric Capacity and facing this issue. We also tried with the new Copy Job item (Preview) and produces the same result: table being added at the Tables root folder, completely ignoring the specified schema. Any news into a potential solution?

In our case, we have seen that the issue is related to source. We are using a connection to an On-Prem Db; however if we copy from Db to parquet and then from parquet to Table, then it is able to properly handle the schema creation.

[frithjof_v]

I'm not able to check right now, but I guess North Europe or Norway.

[Anonymous]

Hi @SimonDalecky 

In response to your question, I ran a series of tests. For the data source, I tried using sample data.

The test results appear to be normal.

At the same time, I have noticed that there are others who have encountered the same problem as you.

You can try one of the methods mentioned here: use the Azure data Factory or the Synapse pipeline.

Copy activity to lakehouse managed table with sche... - Microsoft Fabric Community

The public preview has some limitations that may also affect your use:

https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-schemas#public-preview-limitatio...

Since the function has not been officially launched, the function is not perfect, and there may be some unforeseen problems. Please wait patiently, thank you for your understanding. I will continue to test this problem and hope to help you.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.