Question 211152 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – How to set an array type variable dynamically in Synapse

I want to create an array type variable for use in a pipeline, but don’t know how to do it.
I want to store this month through December in an array type variable.
For example,
I want to create the following variables in the pipeline to be executed this month;

["07","08","09","10","11","12"]

Also, next month is like this;

["08","09","10","11","12"]

Is there any way to create this variable using Synapse’s pipeline function?
Any answer would be helped.
Thank you.

Tags:

2

Answers


  1. 0

    You can use for-each activity and append variable activity inside that to create an array variable with values from the current month to December in Synapse pipeline.

    • Three variables are defined in synapse pipeline

    enter image description here

    1. StartValue: This variable stores the current month.
    2. TotalIterationValue: This variable stores the number of months left in the year.
    3. Array_variable: This variable is an array that stores the months from the current month to December.
    • The variable StartValue and TotalIterationValue are defined using set variable activities.
    1. Set variable- Start Value:
      enter image description here

    This activity sets the value of the StartValue variable to the current month. It uses the substring function to extract the month from the UTC time and sets it as the value of the variable.
    Expression: @substring(utcnow(),5,2).

    1. Set variable – TotalIterationValue:
      enter image description here
      This activity sets the value of the TotalIterationValue variable to the number of months left in the year. It uses the sub function to subtract the current month from 13 (the total number of months in a year) and sets it as the value of the variable.
      Expression: @string(sub(13,int(variables('StartValue'))))
    • Then For-each activity is taken and inside Append variable activity is taken.

    enter image description here

    Expression in items: @range(0, int(variables('TotalIterationValue')))

    enter image description here

    Expression for Array_variable:
    @add(int(variables('StartValue')),item())

    This activity loops through a range of numbers from 0 to the value of TotalIterationValue and appends the corresponding month to the Array_variable variable. It uses the add function to add the current iteration number to the StartValue variable and sets it as the value of the Array_variable variable.

    {
    "name": "PipelineX",
    "properties": {
        "activities": [
            {
                "name": "Set variable- Start Value",
                "type": "SetVariable",
                "dependsOn": [],
                "policy": {
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "variableName": "StartValue",
                    "value": {
                        "value": "@substring(utcnow(),5,2)",
                        "type": "Expression"
                    }
                }
            },
            {
                "name": "Set variable - TotalIterationValue",
                "type": "SetVariable",
                "dependsOn": [
                    {
                        "activity": "Set variable- Start Value",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "policy": {
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "variableName": "TotalIterationValue",
                    "value": {
                        "value": "@string(sub(13,int(variables('StartValue'))))",
                        "type": "Expression"
                    }
                }
            },
            {
                "name": "ForEach1",
                "type": "ForEach",
                "dependsOn": [
                    {
                        "activity": "Set variable - TotalIterationValue",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "userProperties": [],
                "typeProperties": {
                    "items": {
                        "value": "@range(0, int(variables('TotalIterationValue')))",
                        "type": "Expression"
                    },
                    "isSequential": true,
                    "activities": [
                        {
                            "name": "Append variable1",
                            "type": "AppendVariable",
                            "dependsOn": [],
                            "userProperties": [],
                            "typeProperties": {
                                "variableName": "Array_variable",
                                "value": {
                                    "value": "@add(int(variables('StartValue')),item())",
                                    "type": "Expression"
                                }
                            }
                        }
                    ]
                }
            }
        ],
        "variables": {
            "StartValue": {
                "type": "String"
            },
            "TotalIterationValue": {
                "type": "String"
            },
            "Array_variable": {
                "type": "Array"
            }
        },
        "annotations": []
    }
}

    Array_variable will have the values from current month to December.

    Login or Signup to reply.
  2. 0

    Adding to Aswin’s approach, as an alternate , you can try this as well :

    {
"name": "pipeline38",
"properties": {
    "activities": [
        {
            "name": "FindCurrentMonth",
            "type": "SetVariable",
            "dependsOn": [],
            "policy": {
                "secureOutput": false,
                "secureInput": false
            },
            "userProperties": [],
            "typeProperties": {
                "variableName": "FindCurrentMonth",
                "value": {
                    "value": "@substring(formatDateTime(utcnow(),'yyyy-MM-dd'),5,2)",
                    "type": "Expression"
                }
            }
        },
        {
            "name": "Until1",
            "type": "Until",
            "dependsOn": [
                {
                    "activity": "FindCurrentMonth",
                    "dependencyConditions": [
                        "Succeeded"
                    ]
                }
            ],
            "userProperties": [],
            "typeProperties": {
                "expression": {
                    "value": "@equals(variables('TempVar'),'12')",
                    "type": "Expression"
                },
                "activities": [
                    {
                        "name": "Append variable1",
                        "type": "AppendVariable",
                        "dependsOn": [
                            {
                                "activity": "TempVar",
                                "dependencyConditions": [
                                    "Succeeded"
                                ]
                            }
                        ],
                        "userProperties": [],
                        "typeProperties": {
                            "variableName": "appendvariable",
                            "value": {
                                "value": "@variables('TempVar')",
                                "type": "Expression"
                            }
                        }
                    },
                    {
                        "name": "TempVar",
                        "type": "SetVariable",
                        "dependsOn": [],
                        "policy": {
                            "secureOutput": false,
                            "secureInput": false
                        },
                        "userProperties": [],
                        "typeProperties": {
                            "variableName": "TempVar",
                            "value": {
                                "value": "@variables('FindCurrentMonth')",
                                "type": "Expression"
                            }
                        }
                    },
                    {
                        "name": "Update month value",
                        "type": "SetVariable",
                        "dependsOn": [
                            {
                                "activity": "Append variable1",
                                "dependencyConditions": [
                                    "Succeeded"
                                ]
                            }
                        ],
                        "policy": {
                            "secureOutput": false,
                            "secureInput": false
                        },
                        "userProperties": [],
                        "typeProperties": {
                            "variableName": "FindCurrentMonth",
                            "value": {
                                "value": "@{add(int(variables('TempVar')),1)}",
                                "type": "Expression"
                            }
                        }
                    }
                ],
                "timeout": "0.12:00:00"
            }
        },
        {
            "name": "Final Output",
            "type": "SetVariable",
            "dependsOn": [
                {
                    "activity": "Until1",
                    "dependencyConditions": [
                        "Succeeded"
                    ]
                }
            ],
            "policy": {
                "secureOutput": false,
                "secureInput": false
            },
            "userProperties": [],
            "typeProperties": {
                "variableName": "finaloutput",
                "value": {
                    "value": "@variables('appendvariable')",
                    "type": "Expression"
                }
            }
        }
    ],
    "variables": {
        "FindCurrentMonth": {
            "type": "String"
        },
        "month": {
            "type": "String"
        },
        "appendvariable": {
            "type": "Array"
        },
        "TempVar": {
            "type": "String"
        },
        "finaloutput": {
            "type": "Array"
        }
    },
    "annotations": []
}
}

    Create these string variables:
    FindCurrentMonth
    month
    TempVar
    Create these Array variables:
    appendvariable
    finaloutput

    • Use set variable activity to fine the currentmonth by using this expression: @substring(formatDateTime(utcnow(),'yyyy-MM-dd'),5,2)
    • Use until block with expression: @equals(variables('TempVar'),'12')
    • Inside until, you can set a temp variable with expression: @variables('FindCurrentMonth')
    • Use Append variable with exp: @variables('TempVar')
    • Update month value with exp: @{add(int(variables('TempVar')),1)}
    • Outside until, use another set variable to get the final output: @variables('appendvariable')

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search