none
(ADF V2 Datasets) UI does not match Microsoft Tutorials

    Question

  • Where did Parameterizable properties come from?  I am looking at tutorial at the following link.

    I am trying to build my own pipeline via the UI and I cannot get this property to show up, even if I specify the typeProperty in the Advanced tab.

    https://docs.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-multiple-tables-portal



    • Edited by zl34 Thursday, June 7, 2018 7:04 PM
    Monday, June 4, 2018 2:20 PM

All replies

  • UI has been updated but the doc doesn't. Thanks for reporting this issue.

    For the #3 properties, you could just input this into the tableName field of 'Connection' tab. You could click the add dynamic content to input this expression.

    Tuesday, June 5, 2018 3:12 AM
  • Thanks

    melissalevitt@hotmail.com

    Wednesday, June 6, 2018 6:18 PM
  • BTW, I completed the tutorial but it did not work.  There is also an issue with the json for the pipeline. Below is the error.  

    Error Message: The document creation or update failed because there were duplicate properties in the json payload.

    With limited documentation, these tutorials become critical. Can you guys have a look and let us know when fixed?  Both incremental load tutorials is what i've been looking at and neither have worked.

    Thanks

    • Edited by zl34 Wednesday, June 6, 2018 6:39 PM
    Wednesday, June 6, 2018 6:27 PM
  • Are you in git mode or data factory mode? Could you share me the pipeline and dataset json? In the meantime, you could also try copy data tool in the UI for multiple tables scenario.
    Wednesday, June 6, 2018 11:16 PM
  • I am in Data Factory mode.

    The Activity Log shows no error. Would Diagnostic Logs

    provide more descriptive errors from these executions?

    The only error I see is the one displayed by notifications.

    I added a picture of the error below

    { "name": "WatermarkDataset", "properties": { "linkedServiceName": { "referenceName": "AzureSQLDatabaseLinkedService", "type": "LinkedServiceReference" }, "type": "AzureSqlTable", "typeProperties": { "tableName": "[dbo].[watermarktable]" } } } { "name": "SourceDataset", "properties": { "linkedServiceName": { "referenceName": "SqlServerLinkedService", "type": "LinkedServiceReference" }, "type": "SqlServerTable", "typeProperties": { "tableName": "dummyName" } } } { "name": "IncrementalCopyMuliTablePipeline", "properties": { "activities": [ { "name": "IterateSQLTables", "type": "ForEach", "typeProperties": { "items": { "value": "@pipeline().parameters.tableList", "type": "Expression" }, "activities": [ { "name": "LookupOldWaterMarkActivity", "type": "Lookup", "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false }, "typeProperties": { "source": { "type": "SqlSource", "sqlReaderQuery": { "value": "select * from watermarktable where TableName = '@{item().TABLE_NAME}'", "type": "Expression" } }, "dataset": { "referenceName": "WatermarkDataset", "type": "DatasetReference" }, "firstRowOnly": true } }, { "name": "LookupNewWaterMarkActivity", "type": "Lookup", "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false }, "typeProperties": { "source": { "type": "SqlSource", "sqlReaderQuery": { "value": "select MAX(@{item().WaterMark_Column}) as NewWatermarkvalue from @{item().TABLE_NAME}", "type": "Expression" } }, "dataset": { "referenceName": "SourceDataset", "type": "DatasetReference" }, "firstRowOnly": true } }, { "name": "IncrementalCopyActivity", "type": "Copy", "dependsOn": [ { "activity": "LookupOldWaterMarkActivity", "dependencyConditions": [ "Succeeded" ] }, { "activity": "LookupNewWaterMarkActivity", "dependencyConditions": [ "Succeeded" ] } ], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false }, "typeProperties": { "source": { "type": "SqlSource", "sqlReaderQuery": { "value": "select * from @{item().TABLE_NAME} where @{item().WaterMark_Column} > '@{activity('LookupOldWaterMarkActivity').output.firstRow.WatermarkValue}' and @{item().WaterMark_Column} <= '@{activity('LookupNewWaterMarkActivity').output.firstRow.NewWatermarkvalue}'", "type": "Expression" } }, "sink": { "type": "SqlSink", "writeBatchSize": 10000, "sqlWriterStoredProcedureName": { "value": "@{item().StoredProcedureNameForMergeOperation}", "type": "Expression" }, "sqlWriterTableType": { "value": "@{item().TableType}", "type": "Expression" } }, "enableStaging": false, "cloudDataMovementUnits": 0, "enableSkipIncompatibleRow": true, "redirectIncompatibleRowSettings": { "linkedServiceName": { "referenceName": "AzureStorageLinkedService", "type": "LinkedServiceReference" }, "path": "adf" } }, "inputs": [ { "referenceName": "SourceDataset", "type": "DatasetReference" } ], "outputs": [ { "referenceName": "SinkDataset", "type": "DatasetReference", "parameters": { "SinkTableName": "@{item().TABLE_NAME}" } } ] }, { "name": "StoredProceduretoWriteWatermarkActivity", "type": "SqlServerStoredProcedure", "dependsOn": [ { "activity": "IncrementalCopyActivity", "dependencyConditions": [ "Succeeded" ] } ], "policy": { "timeout": "7.00:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false }, "typeProperties": { "storedProcedureName": "[dbo].[sp_write_watermark]", "storedProcedureParameters": { "LastModifiedtime": { "value": { "value": "@{activity('LookupNewWaterMarkActivity').output.firstRow.NewWatermarkvalue}", "type": "Expression" }, "type": "DateTime" }, "TableName": { "value": { "value": "@{activity('LookupOldWaterMarkActivity').output.firstRow.TableName}", "type": "Expression" }, "type": "String" } } }, "linkedServiceName": { "referenceName": "AzureSQLDatabaseLinkedService", "type": "LinkedServiceReference" } } ] } } ], "parameters": { "tableList": { "type": "Object" } } } }





    • Edited by zl34 Thursday, June 7, 2018 7:15 PM
    Thursday, June 7, 2018 7:03 PM
  • The tableList should be "Array" instead of Object. 

    And you could try "debug until" feature in the Portal to debug each activity. Click that red icon, you could set a break point in your pipeline.

    Friday, June 8, 2018 3:08 AM
  • The MS tutorial set it up the Parameter as an Object and when I run it I provide the following. So, this should work.  I am not sure how to change it to an array. If you tried the example can you share that?  Thanks

    {
        "tableList": 
        [
            {
                "TABLE_NAME": "customer_table",
                "WaterMark_Column": "LastModifytime",
                "TableType": "DataTypeforCustomerTable",
                "StoredProcedureNameForMergeOperation": "sp_upsert_customer_table"
            },
            {
                "TABLE_NAME": "project_table",
                "WaterMark_Column": "Creationtime",
                "TableType": "DataTypeforProjectTable",
                "StoredProcedureNameForMergeOperation": "sp_upsert_project_table"
            }
        ]
    }


    Friday, June 8, 2018 1:31 PM
  • In the UI, find the pipeline parameter tab. 
    Friday, June 8, 2018 3:08 PM