locked
Accessing String Array from ADFv2 Split RRS feed

  • Question

  • Hi

    I have the following array from splitting a string in my pipeline

    { "name": "splitConn", "value": [ "Server=tcp:sfzwns5gla.database.windows.net", "Database=SVCutterOil", "user id=xxxxxxxx", "password=xxxxxxxx!!", "Persist Security Info=False", "Asynchronous Processing=True", "" ] }

    I am using this to get the Database Name

    @split(variables('SplitConn')[0], '=')[2]

    When I use this I am getting the following error:

    { "errorCode": "InvalidTemplate", "message": "The expression 'split(variables('SplitConn')[0], '=')[2]' cannot be evaluated because array index '2' is outside bounds (0, 1) of array.", "failureType": "UserError", "target": "Set SourceDBName" }

    Please help!
    Thanks

    Mike Kiser


    Mike Kiser


    • Edited by EMKISER Wednesday, September 4, 2019 4:45 PM
    Wednesday, September 4, 2019 4:44 PM

Answers

  • Hello , 

    This is what I tried in my pipeline  . I took the connectionstring as string 

    conn = ' "name": "splitConn", "value": [ "Server=tcp:sfzwns5gla.database.windows.net", "Database=SVCutterOil", "user id=xxxxxxxx",  "password=xxxxxxxx!!", "Persist Security Info=False", "Asynchronous Processing=True", "" ] } ' 

    Steps

    1)  

    Split the connection string using the 

    @split(variables('conn') ,'['   ) and store the it in sommarray 

    2)Since out point of interest is second part of the conectionstring so , create a variable temp and use the DE (dynamic expression ) 

    @variables('somearray')[1]

    3) split the string from step 2 and put that in a array temp1array 

    @split(variables('temp') ,','   ) 

    4)You have all you need in the arrau and access them using @variables('temp1array')[0] ,@variables('temp1array')[1]

    JSON 

    {
    "name": "Splitting Connection string",
    "properties": {
    "activities": [
    {
    "name": "Split the connection string KV pair",
    "type": "SetVariable",
    "dependsOn": [],
    "userProperties": [],
    "typeProperties": {
    "variableName": "somearray",
    "value": {
    "value": "@split(variables('conn') ,'[' )",
    "type": "Expression"
    }
    }
    },
    {
    "name": "Get servername-dbname",
    "type": "SetVariable",
    "dependsOn": [
    {
    "activity": "Split the connection string KV pair",
    "dependencyConditions": [
    "Succeeded"
    ]
    }
    ],
    "userProperties": [],
    "typeProperties": {
    "variableName": "temp",
    "value": {
    "value": "@variables('somearray')[1]",
    "type": "Expression"
    }
    }
    },
    {
    "name": "Split in an array",
    "type": "SetVariable",
    "dependsOn": [
    {
    "activity": "Get servername-dbname",
    "dependencyConditions": [
    "Succeeded"
    ]
    }
    ],
    "userProperties": [],
    "typeProperties": {
    "variableName": "temp1array",
    "value": {
    "value": "@split(variables('temp') ,',' )",
    "type": "Expression"
    }
    }
    },
    {
    "name": "Server Name",
    "type": "SetVariable",
    "dependsOn": [
    {
    "activity": "Split in an array",
    "dependencyConditions": [
    "Succeeded"
    ]
    }
    ],
    "userProperties": [],
    "typeProperties": {
    "variableName": "ServerName",
    "value": {
    "value": "@variables('temp1array')[0]",
    "type": "Expression"
    }
    }
    },
    {
    "name": "DbName",
    "type": "SetVariable",
    "dependsOn": [
    {
    "activity": "Server Name",
    "dependencyConditions": [
    "Succeeded"
    ]
    }
    ],
    "userProperties": [],
    "typeProperties": {
    "variableName": "DBname",
    "value": "@variables('temp1array')[1]"
    }
    },
    {
    "name": "user Id",
    "type": "SetVariable",
    "dependsOn": [
    {
    "activity": "DbName",
    "dependencyConditions": [
    "Succeeded"
    ]
    }
    ],
    "userProperties": [],
    "typeProperties": {
    "variableName": "userID",
    "value": "@variables('temp1array')[2]"
    }
    },
    {
    "name": "passord",
    "type": "SetVariable",
    "dependsOn": [
    {
    "activity": "user Id",
    "dependencyConditions": [
    "Succeeded"
    ]
    }
    ],
    "userProperties": [],
    "typeProperties": {
    "variableName": "passord",
    "value": "@variables('temp1array')[3]"
    }
    }
    ],
    "variables": {
    "conn": {
    "type": "String",
    "defaultValue": "' \"name\": \"splitConn\", \"value\": [ \"Server=tcp:sfzwns5gla.database.windows.net\", \"Database=SVCutterOil\", \"user id=xxxxxxxx\", \"password=xxxxxxxx!!\", \"Persist Security Info=False\", \"Asynchronous Processing=True\", \"\" ] } '"
    },
    "somearray": {
    "type": "Array"
    },
    "temp": {
    "type": "String"
    },
    "temp1array": {
    "type": "Array"
    },
    "ServerName": {
    "type": "String"
    },
    "DBname": {
    "type": "String"
    },
    "userID": {
    "type": "String"
    },
    "passord": {
    "type": "String"
    }
    },
    "annotations": []
    }

    }


    Thanks Himanshu

    • Proposed as answer by HimanshuSinha-msft Wednesday, September 4, 2019 9:37 PM
    • Marked as answer by EMKISER Thursday, September 5, 2019 1:59 PM
    Wednesday, September 4, 2019 9:13 PM

All replies

  • Hello , 

    This is what I tried in my pipeline  . I took the connectionstring as string 

    conn = ' "name": "splitConn", "value": [ "Server=tcp:sfzwns5gla.database.windows.net", "Database=SVCutterOil", "user id=xxxxxxxx",  "password=xxxxxxxx!!", "Persist Security Info=False", "Asynchronous Processing=True", "" ] } ' 

    Steps

    1)  

    Split the connection string using the 

    @split(variables('conn') ,'['   ) and store the it in sommarray 

    2)Since out point of interest is second part of the conectionstring so , create a variable temp and use the DE (dynamic expression ) 

    @variables('somearray')[1]

    3) split the string from step 2 and put that in a array temp1array 

    @split(variables('temp') ,','   ) 

    4)You have all you need in the arrau and access them using @variables('temp1array')[0] ,@variables('temp1array')[1]

    JSON 

    {
    "name": "Splitting Connection string",
    "properties": {
    "activities": [
    {
    "name": "Split the connection string KV pair",
    "type": "SetVariable",
    "dependsOn": [],
    "userProperties": [],
    "typeProperties": {
    "variableName": "somearray",
    "value": {
    "value": "@split(variables('conn') ,'[' )",
    "type": "Expression"
    }
    }
    },
    {
    "name": "Get servername-dbname",
    "type": "SetVariable",
    "dependsOn": [
    {
    "activity": "Split the connection string KV pair",
    "dependencyConditions": [
    "Succeeded"
    ]
    }
    ],
    "userProperties": [],
    "typeProperties": {
    "variableName": "temp",
    "value": {
    "value": "@variables('somearray')[1]",
    "type": "Expression"
    }
    }
    },
    {
    "name": "Split in an array",
    "type": "SetVariable",
    "dependsOn": [
    {
    "activity": "Get servername-dbname",
    "dependencyConditions": [
    "Succeeded"
    ]
    }
    ],
    "userProperties": [],
    "typeProperties": {
    "variableName": "temp1array",
    "value": {
    "value": "@split(variables('temp') ,',' )",
    "type": "Expression"
    }
    }
    },
    {
    "name": "Server Name",
    "type": "SetVariable",
    "dependsOn": [
    {
    "activity": "Split in an array",
    "dependencyConditions": [
    "Succeeded"
    ]
    }
    ],
    "userProperties": [],
    "typeProperties": {
    "variableName": "ServerName",
    "value": {
    "value": "@variables('temp1array')[0]",
    "type": "Expression"
    }
    }
    },
    {
    "name": "DbName",
    "type": "SetVariable",
    "dependsOn": [
    {
    "activity": "Server Name",
    "dependencyConditions": [
    "Succeeded"
    ]
    }
    ],
    "userProperties": [],
    "typeProperties": {
    "variableName": "DBname",
    "value": "@variables('temp1array')[1]"
    }
    },
    {
    "name": "user Id",
    "type": "SetVariable",
    "dependsOn": [
    {
    "activity": "DbName",
    "dependencyConditions": [
    "Succeeded"
    ]
    }
    ],
    "userProperties": [],
    "typeProperties": {
    "variableName": "userID",
    "value": "@variables('temp1array')[2]"
    }
    },
    {
    "name": "passord",
    "type": "SetVariable",
    "dependsOn": [
    {
    "activity": "user Id",
    "dependencyConditions": [
    "Succeeded"
    ]
    }
    ],
    "userProperties": [],
    "typeProperties": {
    "variableName": "passord",
    "value": "@variables('temp1array')[3]"
    }
    }
    ],
    "variables": {
    "conn": {
    "type": "String",
    "defaultValue": "' \"name\": \"splitConn\", \"value\": [ \"Server=tcp:sfzwns5gla.database.windows.net\", \"Database=SVCutterOil\", \"user id=xxxxxxxx\", \"password=xxxxxxxx!!\", \"Persist Security Info=False\", \"Asynchronous Processing=True\", \"\" ] } '"
    },
    "somearray": {
    "type": "Array"
    },
    "temp": {
    "type": "String"
    },
    "temp1array": {
    "type": "Array"
    },
    "ServerName": {
    "type": "String"
    },
    "DBname": {
    "type": "String"
    },
    "userID": {
    "type": "String"
    },
    "passord": {
    "type": "String"
    }
    },
    "annotations": []
    }

    }


    Thanks Himanshu

    • Proposed as answer by HimanshuSinha-msft Wednesday, September 4, 2019 9:37 PM
    • Marked as answer by EMKISER Thursday, September 5, 2019 1:59 PM
    Wednesday, September 4, 2019 9:13 PM
  • Awesome! This worked perfectly! 

    Thanks Himanshu!
    Mike


    Mike Kiser

    Thursday, September 5, 2019 2:00 PM