Dynamic mapping in Azure Synapse Copy Data Activity

Question

We're currently facing an issue while ingesting data from SQL Server into Avro files. While most of the data types are correctly identified (e.g., strings as strings, integers as integers), our date fields are being mapped as strings instead of dates. For instance, a date like "2012-10-01 14:58:45.8830000" is being treated as a string.

To resolve this, we're exploring the option of dynamically mapping the schema. When we individually pull in tables, the schema inference correctly identifies the date fields. However, since our ingestion process involves pulling in more than one table (its in a for each loop), we want to try dynamically adjust the mapping to accommodate for all 10 tables being pulled in.

We've attempted to set up the dynamic mapping as per our understanding, but it doesn't seem to be working as expected. Any insights or assistance on how to properly configure the dynamic schema mapping would be greatly appreciated. Thank you!

Our attempt at dynamic mapping

Example of correct mapping

Answer 1

As you mentioned you want to implement the dynamically mapping the schema, I have tried the below approach: Step 1: Create a [dbo].[tbl_mappings]

With the Below Columns:

sourceFile sinkTableSchema sinkTableName jsonMapping

Below is the example for the Json Mapping:

{
            "type": "TabularTranslator",
            "mappings": [
                {
                    "source": {
                        "name": "empid",
                        "type": "String",
                        "physicalType": "String"
                    },
                    "sink": {
                        "name": "emp_id",
                        "type": "Int32",
                        "physicalType": "int"
                    }
                },
                {
                    "source": {
                        "name": "empname",
                        "type": "String",
                        "physicalType": "String"
                    },
                    "sink": {
                        "name": "emp_name",
                        "type": "String",
                        "physicalType": "varchar"
                    }
                },
                {
                    "source": {
                        "name": "gender",
                        "type": "String",
                        "physicalType": "String"
                    },
                    "sink": {
                        "name": "Gender",
                        "type": "String",
                        "physicalType": "varchar"
                    }
                },
                {
                    "source": {
                        "name": "depid",
                        "type": "String",
                        "physicalType": "String"
                    },
                    "sink": {
                        "name": "dep_id",
                        "type": "Int32",
                        "physicalType": "int"
                    }
                }
            ]
}

Step 2:

Use the below expression in the dynamic content:

@activity('Lookup1').output.value

@item().jsonMapping

As you mentioned you are facing an issue while ingesting data from SQL Server into Avro files with date column being treated as a string.

Use DerivedColumn transformation to your pipeline