none
SQL DW datasets in Azure Data factory via ARM template RRS feed

  • Question

  • Hi,

    I am trying to create a dataset in ADF using ARM template.

    While creating the dataset, I've specified the value for the table which looks like "[schema].[table]". But this is not working.

    I initially assumed that the error is due to the way it is given. So I made a concat like below

    "[concat('[', parameters('schema'), '].[', parameters('table'), ']')]"

    When I execute the template, the deployment is successful however when I verify the "Table" in the dataset, it displays as "None"

    Can you please assist me if I'm missing something?

    Thank you.

    Here is the template:

    {
    "name": "[concat(parameters('adfName'), '/', parameters('datasetName'))]",
    "type": "Microsoft.DataFactory/factories/datasets",
    "apiVersion": "2018-06-01",
    "properties": {
    "linkedServiceName": {
                        "referenceName": "[parameters('LinkedServiceName')]",
                        "type": "LinkedServiceReference"
                    },
    "type": "AzureSqlDWTable",
    "typeproperties": {
                    "tableName": "[concat('[', parameters('schema'), '].[', parameters('table'), ']')]"
                    }
    }
    }


    • Edited by Prasanth E Thursday, June 27, 2019 11:53 AM
    Thursday, June 27, 2019 11:53 AM

All replies

  • Hello Prasanth E, and thank you for your inquiry.  Before attempting to help you, I would like to confirm/clarify a few things.

    Are you using Data Factory V1 or V2 ?

    Are you using the UI or Powershell or a SDK?

    Does this template contain code needing to be evaluated, and if so, by what?

    Thursday, June 27, 2019 5:48 PM
    Moderator
  • Hello Martin,

    Thank you for the reply.

    We are using Data factory V2 and we are creating the ADF datasets, pipelines, triggers using ARM templates.

    The template provided in the initial description(only the resources section) is the template I used to create the datasets of Azure SQL DW.

    The SQL DW has a bunch of tables. If I'm doing manually, I'm able to select the tables from the drop down. But the table is not getting selected when I specify the same table in the template. While executing the template, the template runs successfully creating the datasets but its just that the Table shows "None".

    Thank you once again for your reply.


    • Edited by Prasanth E Friday, June 28, 2019 7:01 AM
    Friday, June 28, 2019 6:59 AM
  • Here are the steps I took:

    Using an existing datafactory, I created a linked service and dataset for a data warehoues.
    Then I took the code for that dataset, and used it to create the JSON for a new dataset, which used a table.

    {
        "name": "ware2",
        "properties": {
            "linkedServiceName": {
                "referenceName": "AzureSqlDW1",
                "type": "LinkedServiceReference"
            },
            "annotations": [],
            "type": "AzureSqlDWTable",
            "schema": [],
            "typeProperties": {
                "tableName": "[dbo].[DimCustomer]"
            }
        }
    }

    Then I saved this JSON to my local computer, and used powershell command Set-AzDataFactoryV2Dataset to use the local file to create a new Dataset in my existing datafactory.  After refreshing the UI, I inspected and saw the table field was populated.

    Could you please write the steps you took, so I may attempt to reproduce it?

    Monday, July 1, 2019 10:15 PM
    Moderator
  • Thank you Martin for your response.

    I tried what you've said. I tried executing the template from Azure devops services.

    I've mentioned the table name in the similar way that you've mentioned, I got an error "'Unable to parse language expression dbo].[DimCustomer: expected token 'LeftParenthesis' and actual 'RightSquareBracket'.' 

     (using your example for explanation. I used the table name that is present in the SQLDW of ours)

    Still it didn't work. I've used concat to make to look like [dbo].[DimCustomer] in the end and this time the template execution was successful but the table was showing as "None"

    Thank you for your help


    • Edited by Prasanth E Wednesday, July 3, 2019 7:27 AM
    Wednesday, July 3, 2019 7:27 AM
  • Hello Prasanth E.  Instead of 
    {
    
    "value": "@concat('[', dataset().schema, '].[', dataset().table, ']')",
    
    "type": "Expression"
    
     }

    Could you please try

    "typeProperties": {
    
    "tableName": {
    
    "value": "@concat('[', dataset().schema, '].[', dataset().table, ']')",
    
    "type": "Expression"
    
     }
    
     }
    
    
    
    Thursday, July 11, 2019 10:40 PM
    Moderator
  • Ignore the previous post.  I noticed something. I exported ARM template of one of my factories which include an AzureSqlDW, and I looked at how the tablename is specified.
    Here is excerpt:

               "name": "[concat(parameters('factoryName'), '/wr2')]",
                "type": "Microsoft.DataFactory/factories/datasets",
                "apiVersion": "2018-06-01",
                "properties": {
                    "linkedServiceName": {
                        "referenceName": "AzureSqlDW1",
                        "type": "LinkedServiceReference"
                    },
                    "annotations": [],
                    "type": "AzureSqlDWTable",
                    "typeProperties": {
                        "tableName": "[[dbo].[DimCustomer]"
                    }
                },
                "dependsOn": [
                    "[concat(variables('factoryId'), '/linkedServices/AzureSqlDW1')]"
                ]

    Note that there are TWO open-brackets ([[).  I did not alter the file.  I know mine has a hardcoded name, but the quirk remains.  Can you try adding an extra open-bracket on yours?

    I know when I made typos on the table name in the past, the table name came up blank/none.


    Thursday, July 11, 2019 11:54 PM
    Moderator
  • Yes, I have verified this is the cause.  When I deployed with [dbo].[DimCustomer] I got your error.
    When I deployed with [[dbo].[DimCustomer]] the deploy succeeded, but the tablename showed up like so:

    You can see it has extra closing bracket, and the error message when I tried to preview is backwards.

    This leaves the one remaining possibility of [[dbo].[DimCustomer]

    I have no worthy explanation for this behavior.  I am bringing it to attention internally.  Thank you for bringing this issue to me.  It has been quite illuminating.

    Friday, July 12, 2019 5:19 AM
    Moderator
  • I found our that the extra [open bracket[ is to prevent the ARM template from considering the bracket as a function.
    Wednesday, July 17, 2019 11:00 PM
    Moderator