adf collection reference with 1 row
Using ADF Collection reference we transfer this into json format . whilst this works for muliple records . It fails with 1 xml record.
The error is ErrorCode=UserErrorInvalidPathForCollectionReferenceNode,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Invalid json path '$['file-data']['files']['row']' for collection reference node
The sample file for 2 rows that works is below
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<file-data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<files>
<row record="01">
<FilePath>/MYDIRECTORY/MYTest/</FilePath>
<ValidatedBy>Joe Blogg</ValidatedBy>
</row>
<row record="02">
<FilePath>/MYDIRECTORY/MYTest01/</FilePath>
<ValidatedBy>Jose</ValidatedBy>
</row>
</files>
</file-data>
This does not work .
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<file-data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<files>
<row record="01">
<FilePath>/MYDIRECTORY/MYTest/</FilePath>
<ValidatedBy>Joe Blogg</ValidatedBy>
</row>
</files>
</file-data>
sample output required
{"FilePath":"/MYDIRECTORY/MYTest/","ValidatedBy":"Joe Blogg"}
This has been reported here too Azure Data Factory - Collection reference failing when only 1 record
any suggestions
When defining collection reference as $['file-data']['files']['row'] if there is only 1 row it treats as object not as array that may be the reason to get above error. To get your required output follow below procedure:
Go to mapping make Collection reference as empty, enable Advanced editor and Define columns as below:
Filepath --> $['file-data']['files']['row']['FilePath']
ValidatedBy --> $['file-data']['files']['row']['ValidatedBy']
The you will get the Json as shown below:
{"FilePath":"/MYDIRECTORY/MYTest/","ValidatedBy":"Joe Blogg"}
update:
Use lookup activity to read the file. Count the rows in file using @length(activity('Lookup1').output.value[0]['file-data'].files.row)
After reading the file add if activity to the lookup activity with condition:
@greater(length(activity('Lookup1').output.value[0]['file-data'].files.row),1)
To check the row count is greater than 1 or not. If true add copy activity with required source with below mapping:
If false add copy activity with required source with below mapping:
Then it will work according to the row count. Here is the pipeline Json for your requirement:
{
"name": "pipeline4",
"properties": {
"activities": [
{
"name": "Lookup1",
"type": "Lookup",
"dependsOn": [],
"policy": {
"timeout": "0.12:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"userProperties": [],
"typeProperties": {
"source": {
"type": "XmlSource",
"storeSettings": {
"type": "AzureBlobFSReadSettings",
"recursive": true,
"enablePartitionDiscovery": false
},
"formatSettings": {
"type": "XmlReadSettings",
"validationMode": "none",
"namespaces": true
}
},
"dataset": {
"referenceName": "Xmlmulr",
"type": "DatasetReference"
},
"firstRowOnly": false
}
},
{
"name": "If Condition1",
"type": "IfCondition",
"dependsOn": [
{
"activity": "Lookup1",
"dependencyConditions": [
"Succeeded"
]
}
],
"userProperties": [],
"typeProperties": {
"expression": {
"value": "@greater(length(activity('Lookup1').output.value[0]['file-data'].files.row),1)",
"type": "Expression"
},
"ifFalseActivities": [
{
"name": "Copy data1_copy1",
"type": "Copy",
"dependsOn": [],
"policy": {
"timeout": "0.12:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"userProperties": [],
"typeProperties": {
"source": {
"type": "XmlSource",
"storeSettings": {
"type": "AzureBlobFSReadSettings",
"recursive": true,
"enablePartitionDiscovery": false
},
"formatSettings": {
"type": "XmlReadSettings",
"validationMode": "none",
"namespaces": true
}
},
"sink": {
"type": "JsonSink",
"storeSettings": {
"type": "AzureBlobFSWriteSettings"
},
"formatSettings": {
"type": "JsonWriteSettings"
}
},
"enableStaging": false,
"translator": {
"type": "TabularTranslator",
"mappings": [
{
"source": {
"path": "$['file-data']['files']['row']['FilePath']"
},
"sink": {
"path": "FilePath"
}
},
{
"source": {
"path": "$['file-data']['files']['row']['ValidatedBy']"
},
"sink": {
"path": "ValidatedBy"
}
}
],
"collectionReference": ""
}
},
"inputs": [
{
"referenceName": "Xml1",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "Json1",
"type": "DatasetReference"
}
]
}
],
"ifTrueActivities": [
{
"name": "Copy data1",
"type": "Copy",
"dependsOn": [],
"policy": {
"timeout": "0.12:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"userProperties": [],
"typeProperties": {
"source": {
"type": "XmlSource",
"storeSettings": {
"type": "AzureBlobFSReadSettings",
"recursive": true,
"enablePartitionDiscovery": false
},
"formatSettings": {
"type": "XmlReadSettings",
"validationMode": "none",
"namespaces": true
}
},
"sink": {
"type": "JsonSink",
"storeSettings": {
"type": "AzureBlobFSWriteSettings"
},
"formatSettings": {
"type": "JsonWriteSettings"
}
},
"enableStaging": false,
"translator": {
"type": "TabularTranslator",
"mappings": [
{
"source": {
"path": "['FilePath']"
},
"sink": {
"path": "FilePath"
}
},
{
"source": {
"path": "['ValidatedBy']"
},
"sink": {
"path": "ValidatedBy"
}
}
],
"collectionReference": "$['file-data']['files']['row']"
}
},
"inputs": [
{
"referenceName": "Xmlmulr",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "Json1",
"type": "DatasetReference"
}
]
}
]
}
}
],
"variables": {
"rowcount": {
"type": "Integer"
},
"row": {
"type": "String"
},
"r1": {
"type": "Array"
}
},
"annotations": []
}
}
- How do I pull the last modified file with data flow in azure data factory?
- ADF Script Object using Snowflake Connector doesn't allow script parameters
- Split multiple tables from a single sheet in excel using data flows
- Trouble Passing Boolean Values in Azure Data Factory JSON Template
- Check if two files are existing in SFTP using ADF
- Using parameters and dynamic content in pre-SQL script for Azure Data Factory data flow sink transformation
- The Format of Body for a POST request in Web Activity in Azure Data Factory
- adf skip line count issue with csv having empty string
- How can I get the last day of a month in dynamic content in ADF2?
- How to use Azure Data Factory to take Business Central data and put into Azure SQL DB?
- Azure Data Factory (ADF) validate data types from incoming csv
- Running Code in Azure Repo from Azure Data Factory
- Data Filtering Issues
- How to generate .jmx file for ADF pipeline
- Azure Data Factory (ADF) create an alert to notify me when a pipeline is triggered
- Azure Data Factory (ADF) trying to delete a csv file. Error Invalid delete activity payload with 'folderPath' that is required and cannot be empty
- ADF Pipeline Scheduled Hourly Fails Occasionally with Lookup Activity Fetching Last Ingested Date from Databricks Catalog
- Suddendly error in CI CD Azure Data Factory and AzureDevOps
- Migrating current legacy driver to the latest version on ADF link service
- Fabric datafactory copy data to ingest Sharepoint files
- adf collection reference with 1 row
- Creating a ADLS Linked Service with Bicep for a Blob Container?
- Dataset parameter not available in Copy Data Activity (source tab)
- How to retrieve a List of Pipelines in azure data factory for a Specific Branch?
- How to pass an array parameter into an ADF dataflow
- How to replace a single quote with double quotes in ADF dynamic expressions
- Azure Data Factory (ADF) Is it possible to compare the schema of a .csv file against the schema of a database before writing to it
- Azure Data Factory: "Quote all text" inactive in Copy Data activity
- ADF Script Creating a Table with Parquet format using INFER_SCHEMA in Snowflake
- Azure Data Factory (ADF) Failed starting trigger after 5 attempts