CODEWITHSUNDEEP
azure-data-factory
paone
paone

Reputation: 937

Azure data factory pass activity output to a dataset

I am using a SQL Server query which would return the last 3 months since a customer last purchased a product. For instance, There's a customer 100 that last made a purchase in August 2022. The SQL query will return June, July, August. Which would be in the format 062022, 072022, 082022. Now I need to be able to pass these values to the Copy data activity REST api dataset Relative URL (/salemonyr/062022) in the ForEach activity.

enter image description here

So during the first iteration the Relative URL should be set to /salemonyr/062022 the second would be /salemonyr/072022 and third /salemonyr/082022.

Error: The expression 'length(activity('MonYear').output.value)' cannot be evaluated because property 'value' doesn't exist, available properties are 'resultSetCount, recordsAffected, resultSets, outputParameters, outputLogs, outputLogsLocation, outputTruncated, effectiveIntegrationRuntime, executionDuration, durationInQueue, billingReference

Script activity json:

{
    "resultSetCount": 1,
    "recordsAffected": 0,
    "resultSets": [
        {
            "rowCount": 3,
            "rows": [
                {
                    "MonYear": 062022
                },
                {
                    "MonYear": 072022
                },
                {
                    "MonYear": 082022
                }
            ]
        }
    ],
    "outputParameters": {},
    "outputLogs": "",
    "outputLogsLocation": "",
    "outputTruncated": false,
    "effectiveIntegrationRuntime": "",
    "executionDuration": 0,
    "durationInQueue": {
        "integrationRuntimeQueue": 3
    },
    "billingReference": {
        "activityType": "PipelineActivity",
        "billableDuration": [
            {
                "meterType": "",
                "duration": 0.016666666666666666,
                "unit": "Hours"
            }
        ]
    }
}

How would I accomplish this to read the values dynamically from the SQL query.

Upvotes: 0

Views: 410

Answers (2)

Rakesh Govindula
Rakesh Govindula

Reputation: 11234

You can use REST Dataset parameter and use it in the Relative URL.

enter image description here

Relative URL:

enter image description here

Give lookup output to ForEach. use your query in lookup.

enter image description here

Give this to ForEach and inside ForEach, in copy sink(REST DATASET) use the below expression for the dataset parameter.

/salemonyr/@{item().sample_date}

enter image description here

In source, you can give your source.

By this, you can copy the data to the respective Relative URL.

Upvotes: 1

AnnuKumari
AnnuKumari

Reputation: 563

You can use @split(item().colname,',')[0] , split(item().colname,',')[1] and split(item().colname,',')[2] in the relative URL path.

Check the below video for details:

enter image description here

Upvotes: 1

Related Questions