Azure-docs: fileExtension doesn't seem to work

[pes-kapacity]

We're trying to copy files from an SFTP folder to our blob storage. The source and sink are set up without a filename. I specified the .txt extension, but when running my pipeline, the blob storage shows the files being created as .csv. According to the documentation, they ought to use the extension supplied in the File Extension.

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 2309a152-b9bc-7876-e010-b3bde6945d42
• Version Independent ID: 751c7679-b01c-301d-8211-30f43ff50c54
• Content: Delimited text format in Azure Data Factory
• Content Source: articles/data-factory/format-delimited-text.md
• Service: data-factory
• GitHub Login: @linda33wj
• Microsoft Alias: jingwang

[CHEEKATLAPRADEEP-MSFT]

@pes-kapacity Thanks for the question! We are investigating and will update you shortly.

[MartinJaffer-MSFT]

Hello @pes-kapacity and thank you for your inquiry. I believe what you said is correct, however just to confirm my understanding, could you please share some screenshots or code? Sharing this will aid in my attempt to reproduce and/or troubleshoot your issue.

[pes-kapacity]

Hi @MartinJaffer-MSFT. Thank you for picking it up :)

Here's the code (Mapping were removed to keep things brief):

{
"name": "CopyToBlob",
"type": "Copy",
"dependsOn": [
{
"activity": "Set LoadStart",
"dependencyConditions": [
"Succeeded"
]
}
],
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"userProperties": [],
"typeProperties": {
"source": {
"type": "DelimitedTextSource",
"storeSettings": {
"type": "SftpReadSetting",
"recursive": false,
"wildcardFileName": "*.csv",
"modifiedDatetimeStart": {
"value": "@{variables('LastLoaded')}",
"type": "Expression"
}
},
"formatSettings": {
"type": "DelimitedTextReadSetting",
"skipLineCount": 1
}
},
"sink": {
"type": "DelimitedTextSink",
"storeSettings": {
"type": "AzureBlobStorageWriteSetting"
},
"formatSettings": {
"type": "DelimitedTextWriteSetting",
"quoteAllText": true,
"fileExtension": ".txt"
}
},
"enableStaging": false,
"translator": {
"type": "TabularTranslator",
"mappings": [

]
}
},
"inputs": [
{
"referenceName": "ChannelData_Customer",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "ChannelData_CustomerData",
"type": "DatasetReference"
}
]
},

[MartinJaffer-MSFT]

@pes-kapacity I have reproduced the issue. In my blob storage, my test file had ending 'csv' when it should have been 'txt'. Thank you for providing those details. I will forward this to internal teams, and update here when I know more.

[MartinJaffer-MSFT]

Hello @pes-kapacity . This feature is a bit misleading in how it is presented. Currently, the 'fileExtension' only takes effect when all three of the following conditions are true:

• sinkFileName is empty
• sourceFileName is empty
• copyBehavior is _not_ PreserveHierarchy

The product group is aware of the issue. At this moment, there is no timeline for a change.

Would you like me to work with you and find a suitable work-around?

[pes-kapacity]

Hi Martin,

Thanks for getting back to me. All those conditions are true in our case:

Here is the source:

Sink:

And this is the copy behavior:

So we meet all three conditions, but still see .csv files in the blob storage, rather than txt files.

Thanks 😊

Best,

Peter

---

From: MartinJaffer-MSFT [e-mail]
Sent: Wednesday, July 31, 2019 3:54:34 AM
To: MicrosoftDocs/azure-docs [e-mail]
Cc: Peter Schmitz [e-mail]; Mention [e-mail]
Subject: Re: [MicrosoftDocs/azure-docs] fileExtension doesn't seem to work (#35926)

Hello @pes-kapacityhttps://github.com/pes-kapacity . This feature is a bit misleading in how it is presented. Currently, the 'fileExtension' only takes effect when all three of the following conditions are true:

• sinkFileName is empty
• sourceFileName is empty
• copyBehavior is not PreserveHierarchy

The product group is aware of the issue. At this moment, there is no timeline for a change.

Would you like me to work with you and find a suitable work-around?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/MicrosoftDocs/azure-docs/issues/35926?email_source=notifications&email_token=AMW6YTFL4K4E3H4BC46C56LQCDWFVA5CNFSM4IHDOHM2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD3F2BWI#issuecomment-516661465, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AMW6YTDQTPZPGLTSLND4XODQCDWFVANCNFSM4IHDOHMQ.

[MartinJaffer-MSFT]

Hi Peter, @pes-kapacity . When you send an email to Github, the picture are stripped out, replaced by their names. Could you please go to GitHub in your browser and insert your pictures that way?

[pes-kapacity]

Hi Martin,

I just tried editing the earlier post. The links to the images seem to work for me. I hope you can see them, too. If not, please let me know.

Thank you.

Best,

Peter

[MartinJaffer-MSFT]

Thank you. They are now links. That works for me. I will now escalate.

[MartinJaffer-MSFT]

@pes-kapacity Could you share with me a pipeline run ID and activity run ID? You can get this by triggering the pipeline, and then going to the monitoring screen. For this, do not use a 'debug' run. Once I have these ID's I can send them to someone who can look into the back-end logs.

[pes-kapacity]

Hi Martin,

None of this has been published yet, as we're still developing it. I have another case related to the same branch that's basically preventing us from pushing this into a PR. So for the time being, the best I can do is run it in debug mode and provide you with the runID from there. That's what we have been doing with the other ticket, and that seems to give the team enough to go by.

Thanks.

Peter

---

From: MartinJaffer-MSFT [e-mail]
Sent: Friday, August 2, 2019 8:59:15 PM
To: MicrosoftDocs/azure-docs [e-mail]
Cc: Peter Schmitz [e-mail]; Mention [e-mail]
Subject: Re: [MicrosoftDocs/azure-docs] fileExtension doesn't seem to work (#35926)

@pes-kapacityhttps://github.com/pes-kapacity Could you share with me a pipeline run ID and activity run ID? You can get this by triggering the pipeline, and then going to the monitoring screen. For this, do not use a 'debug' run. Once I have these ID's I can send them to someone who can look into the back-end logs.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/MicrosoftDocs/azure-docs/issues/35926?email_source=notifications&email_token=AMW6YTBYGMGDIGAQUS5PIZLQCR7YHA5CNFSM4IHDOHM2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD3OSPAI#issuecomment-517810049, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AMW6YTECOUCIMI34DHLNYNDQCR7YHANCNFSM4IHDOHMQ.

[MartinJaffer-MSFT]

Okay. I will take the debug mode ID's. What is the other ticket, if I may ask?

[pes-kapacity]

Try this one (I just ran it):

Pipeline Run ID: 649ca0be-d7d4-4ca8-9f52-ffa34e37922d

The task called "Copy Data" holds the Delimiter source object. The flow is actually working fine as is. The reason we tried saving them to text files is because a colleague mentioned the other issue could be solved by sourcing the file as a text-file rather than a delimited file. However, I tried simply renaming the file(s) on the FTP Server to .txt instead of.csv, and that did absolutely nothing.

The other issue is related to another pipeline within the same branch. Like the pipeline you just received the RunID for, we source delimited files from an SFTP server, write them t our blob storage (for archiving purposes), and then source them from the BLOB. At least, that was the plan. We have run into issues with these other files. The source files are delimited with a "~" symbol. However, we have experienced numerous cases where the files won't process, and Data Factory specifies an error that the file contains more columns than it expects. We thought we narrowed it down to some of the text entries containing a semi-colon, but I've now seen it fail on totally normal files, too.

I managed to sometimes get individual files processed correctly if I played with the advanced settings (degree of parallelism and number of DIUs), but we have so far not managed to get it to work on the entire folder. Seeing these files are crucial to our customer's sales processes, we really need to have figured out what's going wrong. For the time being, we will rely on an on-prem SSIS package to source them, but ideally, we'd be able to simply process them within Azure, so we can slowly replace the on-prem solutions with cloud-based ones.

There's a thread on it here:

https://social.msdn.microsoft.com/Forums/en-US/be063cd5-b02a-4f9f-91a7-41bbeacef341/adf-v2-issue-with-delimiter-when-dealing-with-a-connectionthrottled-sftp-server-and-big-files?forum=AzureDataFactory

And we have been in touch with a few support people in (presumably) China. We also were granted a free ticket, but neither my direct customer nor myself have the necessary permissions to see the page required to file a ticket (and the 1 person who could grant us the permissions is on vacation), so unfortunately I do not have an exact ticket number for you.

Thanks!

Best regards,

Peter

---

From: MartinJaffer-MSFT [e-mail]
Sent: Friday, August 2, 2019 9:07:36 PM
To: MicrosoftDocs/azure-docs [e-mail]
Cc: Peter Schmitz [e-mail]; Mention [e-mail]
Subject: Re: [MicrosoftDocs/azure-docs] fileExtension doesn't seem to work (#35926)

Okay. I will take the debug mode ID's. What is the other ticket, if I may ask?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/MicrosoftDocs/azure-docs/issues/35926?email_source=notifications&email_token=AMW6YTERVHZ7VJU5M5YQON3QCSAXRA5CNFSM4IHDOHM2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD3OTBLI#issuecomment-517812397, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AMW6YTFR2UYITOEWRF2U27DQCSAXRANCNFSM4IHDOHMQ.

[pes-kapacity]

Heh.

The pipeline RunID I just provided you with also threw the error. That's a first for this set of files. So far, I've only seen it on the sales files.

That's a bummer. I thought we had 2 out of the 3 sets of files nailed. I'll have to investigate some time next week what happened.

/Peter

---

From: Peter Schmitz [e-mail]
Sent: Friday, August 2, 2019 9:20:19 PM
To: MicrosoftDocs/azure-docs [e-mail]; MicrosoftDocs/azure-docs [e-mail]
Cc: Mention [e-mail]
Subject: Re: [MicrosoftDocs/azure-docs] fileExtension doesn't seem to work (#35926)

Try this one (I just ran it):

Pipeline Run ID: 649ca0be-d7d4-4ca8-9f52-ffa34e37922d

The task called "Copy Data" holds the Delimiter source object. The flow is actually working fine as is. The reason we tried saving them to text files is because a colleague mentioned the other issue could be solved by sourcing the file as a text-file rather than a delimited file. However, I tried simply renaming the file(s) on the FTP Server to .txt instead of.csv, and that did absolutely nothing.

The other issue is related to another pipeline within the same branch. Like the pipeline you just received the RunID for, we source delimited files from an SFTP server, write them t our blob storage (for archiving purposes), and then source them from the BLOB. At least, that was the plan. We have run into issues with these other files. The source files are delimited with a "~" symbol. However, we have experienced numerous cases where the files won't process, and Data Factory specifies an error that the file contains more columns than it expects. We thought we narrowed it down to some of the text entries containing a semi-colon, but I've now seen it fail on totally normal files, too.

I managed to sometimes get individual files processed correctly if I played with the advanced settings (degree of parallelism and number of DIUs), but we have so far not managed to get it to work on the entire folder. Seeing these files are crucial to our customer's sales processes, we really need to have figured out what's going wrong. For the time being, we will rely on an on-prem SSIS package to source them, but ideally, we'd be able to simply process them within Azure, so we can slowly replace the on-prem solutions with cloud-based ones.

There's a thread on it here:

https://social.msdn.microsoft.com/Forums/en-US/be063cd5-b02a-4f9f-91a7-41bbeacef341/adf-v2-issue-with-delimiter-when-dealing-with-a-connectionthrottled-sftp-server-and-big-files?forum=AzureDataFactory

And we have been in touch with a few support people in (presumably) China. We also were granted a free ticket, but neither my direct customer nor myself have the necessary permissions to see the page required to file a ticket (and the 1 person who could grant us the permissions is on vacation), so unfortunately I do not have an exact ticket number for you.

Thanks!

Best regards,

Peter

---

From: MartinJaffer-MSFT [e-mail]
Sent: Friday, August 2, 2019 9:07:36 PM
To: MicrosoftDocs/azure-docs [e-mail]
Cc: Peter Schmitz [e-mail]; Mention [e-mail]
Subject: Re: [MicrosoftDocs/azure-docs] fileExtension doesn't seem to work (#35926)

Okay. I will take the debug mode ID's. What is the other ticket, if I may ask?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/MicrosoftDocs/azure-docs/issues/35926?email_source=notifications&email_token=AMW6YTERVHZ7VJU5M5YQON3QCSAXRA5CNFSM4IHDOHM2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD3OTBLI#issuecomment-517812397, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AMW6YTFR2UYITOEWRF2U27DQCSAXRANCNFSM4IHDOHMQ.

[MartinJaffer-MSFT]

Ahh I do have an idea for you now. I don't think they meant changing the file extension, but instead using a different connector. I can't imagine the file suffix imacting anythign. You see, go to look at the JSON definition of your activitiy and datasets. You will find "Type":"DelimitedText". I believe they meant to try an older module ,"TextFormat".

To see the two different dataset types in the UI... umm, nevermind on that. Looks like things were changed since 2 days ago. I'll share with you some JSON instead.

Here is a dataset I used while attempting to repro and find work-arounds for you.
{ "name": "RebexSourceNotDelimited", "properties": { "linkedServiceName": { "referenceName": "Rebexlocal sftp", "type": "LinkedServiceReference" }, "annotations": [], "type": "FileShare", "typeProperties": { "format": { "type": "TextFormat", "columnDelimiter": ",", "rowDelimiter": "", "treatEmptyAsNull": true, "skipLineCount": 0, "firstRowAsHeader": true }, "fileName": "*.csv" } } }
This is an example of an older interface, but I believe is what was meant.

[MartinJaffer-MSFT]

Oh, I found how to still get at the TextFormat in UI.

From here, do not select DelimitedText. Instead choose Json or Avro.

Now, under File format, change from Json/Avro to 'Text Format'. I was thrown off because previously, 'Binary' also directed here.

[pes-kapacity]

That could very well be. I actually looked for a non-delimited text source, but didn't find one. It could very well be my colleague tried that before her vacation, and by the time I tried reproducing it, the source was gone. I highly enjoy the constant changes and improvements, but at times it does make yhou question your own sanity if something you saw the day before suddenly is behaving or looking slightly different 😃

I think my colleague is back next week. I'll have a word with her then, but still hope that we can figure out why we keep running into these errors, and get it resolved in the components.

Thanks :)

/Peter

---

From: MartinJaffer-MSFT [e-mail]
Sent: Friday, August 2, 2019 10:03:25 PM
To: MicrosoftDocs/azure-docs [e-mail]
Cc: Peter Schmitz [e-mail]; Mention [e-mail]
Subject: Re: [MicrosoftDocs/azure-docs] fileExtension doesn't seem to work (#35926)

Oh, I found how to still get at the TextFormat in UI.
[image]https://user-images.githubusercontent.com/46612670/62395518-5a9a7480-b525-11e9-9f0a-b3c4ffdb5cfd.png
From here, do not select DelimitedText. Instead choose Json or Avro.
[image]https://user-images.githubusercontent.com/46612670/62395599-99c8c580-b525-11e9-8792-e12b38da2b57.png
Now, under File format, change from Json/Avro to 'Text Format'. I was thrown off because previously, 'Binary' also directed here.
[image]https://user-images.githubusercontent.com/46612670/62395683-db597080-b525-11e9-9080-1a822d313510.png

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/MicrosoftDocs/azure-docs/issues/35926?email_source=notifications&email_token=AMW6YTEPRTQTLGSSRYPYKE3QCSHI3A5CNFSM4IHDOHM2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD3OWYOQ#issuecomment-517827642, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AMW6YTG3MUPXJG57CXMVFN3QCSHI3ANCNFSM4IHDOHMQ.

[MartinJaffer-MSFT]

Well, you could always just do a binary copy. That moves it as-is. No changes whatsoever.

[MartinJaffer-MSFT]

If you don't mind, I'll close this issue. If need be, we can reopen again later.

[pes-kapacity]

Hi Martin,

Is the issue actually resolved, though? My main reason for opening it to begin with was that the component, specifically the file extension, does not work the way the documentation says it would. It won't help me in this case, but in my opinion, it doesn't work as intended (or the documentation should be updated).

Thanks,

Peter

---

From: MartinJaffer-MSFT [e-mail]
Sent: Friday, August 2, 2019 11:10:04 PM
To: MicrosoftDocs/azure-docs [e-mail]
Cc: Peter Schmitz [e-mail]; Mention [e-mail]
Subject: Re: [MicrosoftDocs/azure-docs] fileExtension doesn't seem to work (#35926)

If you don't mind, I'll close this issue. If need be, we can reopen again later.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/MicrosoftDocs/azure-docs/issues/35926?email_source=notifications&email_token=AMW6YTAIKKEWEIXGULETOIDQCSPCZA5CNFSM4IHDOHM2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD3O3BZQ#issuecomment-517845222, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AMW6YTD33YTVF5XHDAKK37TQCSPCZANCNFSM4IHDOHMQ.

[MartinJaffer-MSFT]

I agree. The feature is misleading. I cited your example, and how you appeared to meet all three conditions. Here is the response I got:

in your scenario (copy from folder to folder), if you specify the copy behavior is None, we will use "PreserveHierarchy" as the default behavior. So the file extension doesn't work. We are preparing to improve the use experience in this scenario and well handle the file extension. Will let you know when we finish this improvement.

[pes-kapacity]

Cool, thank you. That bit with "When None is selected, PreserveHierarchy becomes default" is definitely confusing, lol.

---

From: MartinJaffer-MSFT [e-mail]
Sent: Monday, August 5, 2019 6:55:36 PM
To: MicrosoftDocs/azure-docs [e-mail]
Cc: Peter Schmitz [e-mail]; Mention [e-mail]
Subject: Re: [MicrosoftDocs/azure-docs] fileExtension doesn't seem to work (#35926)

I agree. The feature is misleading. I cited your example, and how you appeared to meet all three conditions. Here is the response I got:

in your scenario (copy from folder to folder), if you specify the copy behavior is None, we will use "PreserveHierarchy" as the default behavior. So the file extension doesn't work. We are preparing to improve the use experience in this scenario and well handle the file extension. Will let you know when we finish this improvement.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/MicrosoftDocs/azure-docs/issues/35926?email_source=notifications&email_token=AMW6YTCJ2GKVMAXQUFTCJE3QDBLQRA5CNFSM4IHDOHM2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD3SNMVA#issuecomment-518313556, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AMW6YTC2IGTZ7B7DSG55SNDQDBLQRANCNFSM4IHDOHMQ.

[MartinJaffer-MSFT]

Did the non-delimited text thing help? I totally agree, changes cause me to question everything too.