none
Activity dataflow1 failed: DF-SYS-01 at Sink RRS feed

  • Question

  • I am using ADFv2 mapping to read from source to destination. Source and target table are in azure sql database. I am simply reading from source into target table. There is not sql script involved just a simple table to table read and write. When i execute the pipeline it throws up this exception " Activity dataflow1 failed: DF-SYS-01 at Sink 'sink1': com.microsoft.sqlserver.jdbc.SQLServerException: Column names in each table must be unique. Column name 'customer' in table 'T_f6115688a44a4b2f986c0b9fba27a35b' is specified more than once."

    I dont know why adf creates a temporary table and this table is not persistent so after the error it disappears. Has anyone encountered this before or its a bug in adf mapping flows since it is still in preview?

    Monday, July 8, 2019 9:17 AM

All replies

  • I am getting this same error message. It is happening on a dataflow sink. 

    { "message": "DF-SYS-01 at Sink 'REOPacificCommitment0DimRealEstateOwnedAsset': com.microsoft.sqlserver.jdbc.SQLServerException: Column names in each table must be unique. Column name 'ASSET_TYPE' in table 'T_c1dc1f865ffc46c69af9537b26a107a2' is specified more than once

    The first time it happened on another column. I went back to the dataset and refreshed the schema, redid the mappings in the DF sink, and got this column reference.

    Here is the JSON for the data flow:

    {
    "name": "CommitmentsFile",
    "properties": {
    "type": "MappingDataFlow",
    "typeProperties": {
    "sources": [
    {
    "dataset": {
    "referenceName": "SVH_Commitments_DropZone",
    "type": "DatasetReference"
    },
    "name": "CommitmentsSpanish"
    },
    {
    "dataset": {
    "referenceName": "JobExecution",
    "type": "DatasetReference"
    },
    "name": "GetJobExecution"
    },
    {
    "dataset": {
    "referenceName": "BusinessDate",
    "type": "DatasetReference"
    },
    "name": "GetBusinessDate"
    }
    ],
    "sinks": [
    {
    "dataset": {
    "referenceName": "Commitments_DLS",
    "type": "DatasetReference"
    },
    "name": "CommitmentsDatalake"
    },
    {
    "dataset": {
    "referenceName": "SVH_REOPacificCommitment_0_DimRealEstateOwnedAsset",
    "type": "DatasetReference"
    },
    "name": "REOPacificCommitment0DimRealEstateOwnedAsset"
    }
    ],
    "script": "\n\nsource(output(\n\t\tUNIDAD_REGISTRAL as string,\n\t\tPROMOCION_COMERCIAL as string,\n\t\tPROMOCION_CONJUNTA as string,\n\t\tDIRECCION as string,\n\t\tPOBLACION as string,\n\t\tPROVINCIA as string,\n\t\tCOMUNIDAD_AUTONOMA as string,\n\t\tTIPO_INMUEBLE as string,\n\t\tHA_BUCKET as string,\n\t\tASSET_TYPE as string,\n\t\tFECHA_DOCUMENTO as string,\n\t\tPRECIO_VENTA_FINAL as string,\n\t\tIMPORTE_TASACION as string,\n\t\tCANAL as string,\n\t\tSUPERFICIE_CONSTRUIDA as string,\n\t\tSUPERFICIE_SOLAR as string,\n\t\tUNIDAD_REGISTRAL_ACTUAL as string,\n\t\tSOCIEDAD_ACTUAL as string,\n\t\tUNIDAD_REGISTRAL_LINK as string,\n\t\tSOCIEDAD_LINK as string,\n\t\tUNIDAD_REGISTRAL_5000 as string,\n\t\tFLAG_CARTERA as string,\n\t\tID_COMPROMISO as string\n\t),\n\tallowSchemaDrift: false,\n\tvalidateSchema: false,\n\trowUrlColumn: 'SourceFile',\n\twildcardPaths:['*COMMITMENTS*.txt']) ~> CommitmentsSpanish\nsource(output(\n\t\tJobExecutionId as string,\n\t\tAdfRunId as string,\n\t\tJobName as string,\n\t\tBatchName as string\n\t),\n\tallowSchemaDrift: false,\n\tvalidateSchema: false) ~> GetJobExecution\nsource(output(\n\t\tBusinessDate as string\n\t),\n\tallowSchemaDrift: true,\n\tvalidateSchema: false) ~> GetBusinessDate\nAddBusinessDate derive(PRECIO_VENTA_FINAL = toDecimal(PRECIO_VENTA_FINAL,10,2,'###,##','es-ES')/100,\n\t\tIMPORTE_TASACION = toDecimal(IMPORTE_TASACION,10, 2,'###,##', 'es-ES')/100,\n\t\tSUPERFICIE_CONSTRUIDA = toDecimal(SUPERFICIE_CONSTRUIDA,10, 2,'###,##', 'es-ES')/100,\n\t\tSUPERFICIE_SOLAR = toDecimal(SUPERFICIE_SOLAR,10, 2,'###,##', 'es-ES')/100,\n\t\tUNIDAD_REGISTRAL = toLong(UNIDAD_REGISTRAL),\n\t\tPROMOCION_COMERCIAL = toLong(PROMOCION_COMERCIAL),\n\t\tPROMOCION_CONJUNTA = toLong(PROMOCION_CONJUNTA),\n\t\tUNIDAD_REGISTRAL_ACTUAL = toLong(UNIDAD_REGISTRAL_ACTUAL),\n\t\tSOCIEDAD_ACTUAL = toLong(SOCIEDAD_ACTUAL),\n\t\tUNIDAD_REGISTRAL_LINK = toLong(UNIDAD_REGISTRAL_LINK),\n\t\tSOCIEDAD_LINK = toLong(SOCIEDAD_LINK),\n\t\tUNIDAD_REGISTRAL_5000 = toLong(UNIDAD_REGISTRAL_5000),\n\t\tFileDate = toDate(regexExtract(SourceFile, '[0-9]{8}', 0),'yyyyMMdd'),\n\t\tJobExecutionId = toInteger(JobExecutionId),\n\t\tID_COMPROMISO = toInteger(ID_COMPROMISO),\n\t\tFECHA_DOCUMENTO = toDate(FECHA_DOCUMENTO, 'yyyy-MM-dd'),\n\t\tSourceFile = '/Commitments/' + iif(like(SourceFile,'%/%'),substring(SourceFile,length(SourceFile) - instr(reverse(SourceFile),'/')+2),SourceFile),\n\t\tBusinessDt = toInteger(BusinessDate)) ~> ConvertDataTypes\nCommitmentsSpanish, GetJobExecution join(true(),\n\tjoinType:'cross',\n\tbroadcast: 'none')~> AddExecutionParms\nAddExecutionParms, GetBusinessDate join(true(),\n\tjoinType:'cross',\n\tbroadcast: 'none')~> AddBusinessDate\nConvertDataTypes sink(input(\n\t\t{UR (Asset ID)} as string,\n\t\t{Commercial Development ID} as string,\n\t\t{Combined Development ID} as string,\n\t\tAddress as string,\n\t\tMunicipality as string,\n\t\tCity as string,\n\t\tRegion as string,\n\t\t{Asset Type} as string,\n\t\t{HA Bucket} as string,\n\t\t{Property Type} as string,\n\t\t{Contract Date} as string,\n\t\t{Sale Price} as string,\n\t\t{Appraisal Value} as string,\n\t\tChannel as string,\n\t\tGCA as string,\n\t\t{Plot Area} as string,\n\t\t{Current UR (Asset ID)} as string,\n\t\t{Current Group} as string,\n\t\t{UR Link} as string,\n\t\t{Group Link} as string,\n\t\t{UR 5000} as string,\n\t\tPortfolio as string,\n\t\t{Contract ID} as string\n\t),\n\tallowSchemaDrift: false,\n\tvalidateSchema: false,\n\trowUrlColumn:'SourceFile',\n\tmapColumn(\n\t\t{UR (Asset ID)} = UNIDAD_REGISTRAL,\n\t\t{Commercial Development ID} = PROMOCION_COMERCIAL,\n\t\t{Combined Development ID} = PROMOCION_CONJUNTA,\n\t\tAddress = DIRECCION,\n\t\tMunicipality = POBLACION,\n\t\tCity = PROVINCIA,\n\t\tRegion = COMUNIDAD_AUTONOMA,\n\t\t{Asset Type} = TIPO_INMUEBLE,\n\t\t{HA Bucket} = HA_BUCKET,\n\t\t{Property Type} = ASSET_TYPE,\n\t\t{Contract Date} = FECHA_DOCUMENTO,\n\t\t{Sale Price} = PRECIO_VENTA_FINAL,\n\t\t{Appraisal Value} = IMPORTE_TASACION,\n\t\tChannel = CANAL,\n\t\tGCA = SUPERFICIE_CONSTRUIDA,\n\t\t{Plot Area} = SUPERFICIE_SOLAR,\n\t\t{Current UR (Asset ID)} = UNIDAD_REGISTRAL_ACTUAL,\n\t\t{Current Group} = SOCIEDAD_ACTUAL,\n\t\t{UR Link} = UNIDAD_REGISTRAL_LINK,\n\t\t{Group Link} = SOCIEDAD_LINK,\n\t\t{UR 5000} = UNIDAD_REGISTRAL_5000,\n\t\tPortfolio = FLAG_CARTERA,\n\t\t{Contract ID} = ID_COMPROMISO\n\t)) ~> CommitmentsDatalake\nConvertDataTypes sink(input(\n\t\tInputId as long,\n\t\tBusinessDt as integer,\n\t\tur_id as integer,\n\t\tcommercial_development_id as integer,\n\t\tcombined_development_id as integer,\n\t\taddress as string,\n\t\tmunicipality as string,\n\t\tcity as string,\n\t\tregion as string,\n\t\tasset_type as string,\n\t\tha_bucket as string,\n\t\tproperty_type as string,\n\t\tcontract_date as date,\n\t\tsale_price as decimal(28,10),\n\t\tappraisal_value as decimal(28,10),\n\t\tchannel as string,\n\t\tgca as decimal(28,10),\n\t\tplot_area as decimal(28,10),\n\t\tcurrent_ur_id as integer,\n\t\tcurrent_group as integer,\n\t\tur_link as integer,\n\t\tgroup_link as integer,\n\t\tur_5000 as integer,\n\t\tportfolio_type as string,\n\t\tcontract_id as integer,\n\t\tJobExecutionId as long,\n\t\tProcessStatusId as integer,\n\t\tValidationDesc as string,\n\t\tCreateDttm as timestamp\n\t),\n\tallowSchemaDrift: true,\n\tvalidateSchema: false,\n\tformat: 'table',\n\tdeletable:false,\n\tinsertable:true,\n\tupdateable:false,\n\tupsertable:false,\n\tmapColumn(\n\t\tBusinessDt,\n\t\tur_id = UNIDAD_REGISTRAL,\n\t\tcommercial_development_id = PROMOCION_COMERCIAL,\n\t\tcombined_development_id = PROMOCION_CONJUNTA,\n\t\taddress = DIRECCION,\n\t\tmunicipality = POBLACION,\n\t\tcity = PROVINCIA,\n\t\tregion = COMUNIDAD_AUTONOMA,\n\t\tasset_type = ASSET_TYPE,\n\t\tproperty_type = TIPO_INMUEBLE,\n\t\tcontract_date = FECHA_DOCUMENTO,\n\t\tsale_price = PRECIO_VENTA_FINAL,\n\t\tappraisal_value = IMPORTE_TASACION,\n\t\tchannel = CANAL,\n\t\tgca = SUPERFICIE_CONSTRUIDA,\n\t\tplot_area = SUPERFICIE_SOLAR,\n\t\tcurrent_ur_id = UNIDAD_REGISTRAL_ACTUAL,\n\t\tcurrent_group = SOCIEDAD_ACTUAL,\n\t\tur_link = UNIDAD_REGISTRAL_LINK,\n\t\tgroup_link = SOCIEDAD_LINK,\n\t\tur_5000 = UNIDAD_REGISTRAL_5000,\n\t\tportfolio_type = FLAG_CARTERA,\n\t\tcontract_id = ID_COMPROMISO,\n\t\tJobExecutionId\n\t)) ~> REOPacificCommitment0DimRealEstateOwnedAsset"
    }
    }
    }

    Monday, July 8, 2019 9:25 PM
  • Hi there,

    I tried copying an Azure SQL table data (Source) to another table in Azure SQL (sink) using ADF V2 Data flow mapping and see no issues with it. I was able to copy the data successfully without any issues.

    Below are the settings I configured in Data flow activity, which worked for me.

    Source:

    Sink:


    If you are still having the issue, could you please share additional details like 'Options' selected in source settings and sink settings and also the mapping settings from source to sink? 


    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered"Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster. ]


    Monday, July 8, 2019 10:13 PM
    Moderator
  • I voted this reply, but I meant to down vote it. 

    Somewhere under the covers, a temporary table is being created by the data flow. And the create table statement has two columns named the same thing. SQL Server is throwing an exception. The fact that you can't reproduce this doesn't indicate that it's not a bug. 

    My data flow worked a dozen times last week. Today, it is broken, and I've touched nothing. 

    Mike

    Monday, July 8, 2019 10:21 PM
  • Hi Mike,

    Sorry you are experiencing this, apologizes for the inconvenience. I am following up with Product engineering team to see if there is any ongoing issue related to Data flow. I will keep you posted once I have an update on this. 

      


    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster. ]

    Monday, July 8, 2019 10:41 PM
    Moderator
  • Hi all,

    Sorry that you are all experiencing this error. The engineering team investigated the issue and discovered a workaround. For any Data Flow that is experiencing the error message reported above, go into the SQL sink and set Allow Schema Drift to be false. Doing so should cause the pipeline to start succeeding (barring no other errors).

    

    Thanks,

    Daniel


    Monday, July 8, 2019 11:13 PM
  • To Follow up,

    Allow schema drift can be set to true, but only if the Automapping is on in the Mapping tab. If a user is explicitly mapping, then schema drift is not allowed and the pipeline will fail.

    This was an oversight on the product group while introducing a new breaking change that is necessary for a new feature we are rolling out shortly. We apologize for not communicating this properly and not sharing proper validation and error messages.

    Thanks,

    Daniel


    Tuesday, July 9, 2019 6:47 PM