azure data factory - Copying 7 column table to 6 column table -

i'm porting sql server integration services packages azure data factory.

i have 2 tables (table 1 , table 2) live on different servers. 1 has 7 columns, other six. followed example @ https://docs.microsoft.com/en-us/azure/data-factory/data-factory-map-columns

table 1 ddl:

create table dbo.table1 (     zonename nvarchar(max),      propertyname nvarchar(max),      basepropertyid int,      dfp_ad_unit_id bigint,      momenttype nvarchar(200),      operatingsystemname nvarchar(50) ) 

table 2 ddl

create table dbo.table2 (     zoneid int identity,      zonename nvarchar(max),      propertyname nvarchar(max),      basepropertyid int,      dfp_ad_unit_id bigint,      momenttype nvarchar(200),      operatingsystemname nvarchar(50) ) 

in adf, define table 1 as:

{   "$schema": "http://datafactories.schema.management.azure.com/schemas/2015-09-01/microsoft.datafactory.table.json",   "name": "table1",   "properties": {     "type": "azuresqltable",     "linkedservicename": "platformx",     "structure": [       { "name": "zonename" },       { "name": "propertyname" },       { "name": "basepropertyid" },       { "name": "dfp_ad_unit_id" },       { "name": "momenttype" },       { "name": "operatingsystemname" }     ],     "external": true,     "typeproperties": {       "tablename": "platform.zone"     },     "availability": {       "frequency": "day",       "interval": 1     }   } } 

in adf define table 2 as:

{   "$schema": "http://datafactories.schema.management.azure.com/schemas/2015-09-01/microsoft.datafactory.table.json",   "name": "table2",   "properties": {     "type": "sqlservertable",     "linkedservicename": "brixdw",     "structure": [       { "name": "zoneid" },       { "name": "zonename" },       { "name": "propertyname" },       { "name": "basepropertyid" },       { "name": "dfp_ad_unit_id" },       { "name": "momenttype" },       { "name": "operatingsystemname" }     ],     "external": true,     "typeproperties": {       "tablename": "staging.dimzone"     },     "availability": {       "frequency": "day",       "interval": 1     }   } } 

as can see, table2 has identity column, automatically populated.

this should simple copy activity:

{   "$schema": "http://datafactories.schema.management.azure.com/schemas/2015-09-01/microsoft.datafactory.pipeline.json",   "name": "copy_table1_to_table2",   "properties": {     "description": "copy_table1_to_table2",     "activities": [       {         "name": "copy_table1_to_table2",         "type": "copy",         "inputs": [           { "name": "table1" }         ],         "outputs": [           {             "name": "table2"           }         ],         "typeproperties": {           "source": {             "type": "sqlsource",             "sqlreaderquery": "select * dbo.table1"           },           "sink": {             "type": "sqlsink"           },           "translator": {             "type": "tabulartranslator",             "columnmappings": "zonename: zonename, propertyname: propertyname, basepropertyid: basepropertyid, dfp_ad_unit_id: dfp_ad_unit_id, momenttype: momenttype, operatingsystemname: operatingsystemname"           }         },         "policy": {           "concurrency": 1,           "executionpriorityorder": "oldestfirst",           "retry": 3,           "timeout": "01:00:00"         },         "scheduler": {           "frequency": "day",           "interval": 1         }       }     ],     "start": "2017-07-23t00:00:00z",     "end": "2020-07-19t00:00:00z"   } } 

i figured not mapping zoneid, ignored. adf giving me following error.

copy activity encountered user error: gatewaynodename=app1250s,errorcode=usererrorinvalidcolumnmappingcolumncountmismatch,'type=microsoft.datatransfer.common.shared.hybriddeliveryexception,message=invalid column mapping provided copy activity: 'zonename: zonename, propertyname: propertyname, basepropertyid: basepropertyid, dfp_ad_unit_id: dfp_ad_unit_id, momenttype: momenttype, operatingsystemname: operatingsystemname', detailed message: different column count between target structure , column mapping. target column count:7, column mapping count:6. check column mapping in table definition.,source=microsoft.datatransfer.common,'

in nutshell i'm trying copy 7 column table 6 column table , data factory doesn't it. how can accomplish task?

one option create view on 7-column table not include identity column , insert view.

create view bulkload.table2 select     zonename,      propertyname,      basepropertyid,      dfp_ad_unit_id,      momenttype,      operatingsystemname go 

i can digging , see if trick possible column mapping should unblock you.

hth