This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(16, 25%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJ%3C/text%3E%3C/svg%3E)
I was able to implement dynamic schema(column) mapping programmatically by specifying the mapping in copy activity -> translator property as mentioned in this
article. (https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping)
I have used Copy data component of Azure Data Factory.
The requirement that I have is that, before uploading the file, the user will do the mapping and these mappings will be saved in the Azure Blob Storage in form of json
file. When the file is uploaded in the Azure Blob Storage, the trigger configured to the pipeline will start the Azure Data Factory pipeline.
Can this be achieved in the same way by setting translator property in Data Flow?
Regards
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(281.6, 65%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAA%3C/text%3E%3C/svg%3E)
I was able to implement dynamic schema(column) mapping programmatically by specifying the mapping in copy activity -> translator property as mentioned in this
can you please share C# code. im aving hard time to find the c# classes to code.
All I am doing is creating a column mapping in json structure(Newtonsoft.Json) as required by ADF using the entities.
I am storing this in one of the blob where in the ADF can read and apply the mapping to Copy Data.
Hope this help!!
This is how I made it work:
I dont think there is a way to achieve this in Data Flow.
So, in Copy Data activity, used the dynamic column mapping to generate a new file (in sink).
Used this new file as source in the Data Flow.
Regards
Jignesh
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 70%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMM%3C/text%3E%3C/svg%3E)
Thank you for sharing your solution @jigsm
can you please tell me how to use dynamic column mapping in copy activity
Yes, data flow has full built-in support for flexible schema mapping
Mark,
Thanks for the reply.
So should I create a Lookup and assign the data set of the json file?
I have reviewed Derived column, so how to assign the output of the json file to Derived column component? or is there any other component that I need to use?
Any pointers will be very helpful.
Regards
Add a source that points to our JSON file, then add a Derived Column. You should see the schema of the file in the Inspect tab on the Derived Column. If you do not see the schema, go to the Source transformation and click "Import Projection".
Mark,
Thanks for the reply.
I have assigned the json file source to DerivedColumn component and able to see all the mappings in the json format.
Please can you guide me on how should I complete the flow with my source as Azure Blob Storage and Sink as Azure SQL Database.
Where should I tie the DerivedColumn component with the Azure Blob Storage?
Thanks for all the help!!!!!
now add a sink transform as the next step after your derived column
Mark,
As shown in the screen shot, I have added Sink Transform next to Derived Column.
In the sink, I have selected 'derivedColumnMapping' as Incoming Stream.
Now which Dataset should I select?
I need to transform data from sourceBlob(delimited txt file ) into Azure SQL Database (sink1).
Also how to consume the json mapping available in derivedColumMapping component in sink1 mapping section?
I have added the json mapping that worked for me with Copy Data component, will this format work with Data Flow sink's mapping?
Can we do a quick screen share?
{
"type": "TabularTranslator",
"mappings": [
{
"source": {
"name": "StudentId",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "StudentId",
"type": "String",
"physicalType": "varchar"
}
},
{
"source": {
"name": "LastName",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "LastName",
"type": "String",
"physicalType": "varchar"
}
},
{
"source": {
"name": "FirstName",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "FirstName",
"type": "String",
"physicalType": "varchar"
}
},
{
"source": {
"name": "BirthDate",
"type": "DateTime",
"physicalType": "String"
},
"sink": {
"name": "BirthDate",
"type": "DateTime",
"physicalType": "date"
}
},
{
"source": {
"name": "Line1",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "AddressLine1",
"type": "String",
"physicalType": "varchar"
}
},
{
"source": {
"name": "Line2",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "AddressLine2",
"type": "String",
"physicalType": "varchar"
}
},
{
"source": {
"name": "AptNumber",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "AptNumber",
"type": "String",
"physicalType": "varchar"
}
},
{
"source": {
"name": "City",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "City",
"type": "String",
"physicalType": "varchar"
}
},
{
"source": {
"name": "State",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "State",
"type": "String",
"physicalType": "varchar"
}
},
{
"source": {
"name": "PostalCode",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "PostalCode",
"type": "String",
"physicalType": "varchar"
}
},
{
"source": {
"name": "GuardianFirstName",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "GuardianFirstName",
"type": "String",
"physicalType": "varchar"
}
},
{
"source": {
"name": "GuardianLastName",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "GuardianLastName",
"type": "String",
"physicalType": "varchar"
}
},
{
"source": {
"name": "HomePhoneNumber",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "HomePhoneNumber",
"type": "String",
"physicalType": "varchar"
}
},
{
"source": {
"name": "WorkPhoneNumber",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "WorkPhoneNumber",
"type": "String",
"physicalType": "varchar"
}
}
]
}
One of our community leads here can probably help you with that
@jigsm , do you still require assistance?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(124.80000000000001, 17%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EVM%3C/text%3E%3C/svg%3E)
@MarkKromer-8019, I am trying to reproduce above steps for my projects.
I too am trying to explicitly pass JSON schema in mapping data flow activity. However, I am unable to add JSON schema in the derived column into the sink. Can you please guide me on how to do this?
