sql-server - Datafactory - dynamically copy subsection of columns from one database table to another

Question

I have a database on SQL Server on premises and need to regularly copy the data from 80 different tables to an Azure SQL Database. For each table the columns I need to select from and map are different - example, TableA - I need columns 1,2 and 5. For TableB I need just column 1. The tables are named the same in the source and target, but the column names are different.

I could create multiple Copy data pipelines and select the source and target data sets and map to the target table structures, but that seems like a lot of work for what is ultimately the same process repeated.

I've so far created a meta table, which lists all the tables and the column mapping information. This table holds the following data: SourceSchema, SourceTableName, SourceColumnName, TargetSchema, TargetTableName, TargetColumnName.

For each table, data is held in this table to map the source tables to the target tables.

I have then created a lookup which selects each table from the mapping table. It then does a for each loop and does another lookup to get the source and target column data for the table in the foreach iteration.

From this information, I'm able to map the Source table and the Sink table in a Copy Data activity created within the foreach loop, but I'm not sure how I can dynamically map the columns, or dynamically select only the columns I require from each source table. I have the "activity('LookupColumns').output" from the column lookup, but would be grateful if someone could suggest how I can use this to then map the source columns to the target columns for the copy activity. Thanks.

标签： sql-serverazure-data-factoryazure-data-factory-2azure-data-factory-pipeline

Answer 1

In your case, you can use the expression in the mapping setting.

It needs your provide an expression and it's data should like this:{"type":"TabularTranslator","mappings":[{"source":{"name":"Id"},"sink":{"name":"CustomerID"}},{"source":{"name":"Name"},"sink":{"name":"LastName"}},{"source":{"name":"LastModifiedDate"},"sink":{"name":"ModifiedDate"}}]}

So you need to add a column named as Translator in your meta table, and it's value should be like the above JSON data. Then use this expression to do mapping:@item().Translator

Reference: https://docs.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping#parameterize-mapping