Data factory run issue

Question

We have a Data Factory Pipeline which reads a CSV from a location, manipulates the data through three Data Flows and writes the data to an Azure SQL db.

The process was initially drafted with a static CSV in Blob Storage since its long term source was not available.

We now have access to the same file but live via SFTP. We’ve created a new Dataset specifying all the SFTP information including passphrase and key file. The file can be previewed through the Preview Data option

SFTP access is via a whitelist and all Data Factory - AustraliaEast IP’s have been added

We’ve switched the Data flow source from the old Blob dataset to the new SFTP dataset and run the pipeline

We get the following error message

Operation on target AddNewAssets failed: {"StatusCode":"DF-Executor-InvalidPath","Message":"Job failed due to reason: at Source 'TIALRMExport': Path /saas-out/CES/Assets/ALRM/TI_ALRM_Export.csv does not resolve to any file(s). Please make sure the file/folder exists and is not hidden. At the same time, please ensure special character is not included in file/folder name, for example, name starting with _","Details":""}

Drilling in further the pipeline fails at the very first step, reading the source Dataset in the first Data flow

The Dataset can be previewed so we don’t understand why it doesn’t exist when it comes to running the pipeline

Is it down to the special character in the folder name? Would this allow the data to be previewed but not run?

Thanks for any guidance given

Paul

Answer 1

Thanks for your help. I found some other forums where a similar issue was being discussed. It led me to try a simple copy operation in a pipeline. This worked!

So the file can be used as a part of a pipeline source, but cannot be used as part of a data flow source.

After exploring every avenue we tried changing the folder and file names

The folder contains saas-out

The file name was TI__ALRM__Export.csv

The folder name was ok but as soon as the underscores were removed from the file name it worked. Looks like Data Flow behaves differently to the rest of Data Factory and has an issue with only some special characters in certain places

Answer 2

@Sedat SALMAN In addition I'm suspecting that the special character (_) in File Name (*ALRM/TI_ALRM_Export.csv*) could be a root cause.

To verify that, in case if you have another file without special characters in the file name in the same SFTP directory, can you try to preview the data of that particular file from data flow source? Looks like this issue is specific to Data flow and SFTP files with special characters in file name.

Please keep us posted how it goes.

Thank you

Answer 3

It's possible that the special character in the folder name is causing the issue during the pipeline execution, even though the data can be previewed in the dataset.

However, before jumping to that conclusion, it's important to verify the following:

1. Double-check the file path: Make sure the file path specified in the dataset matches the actual path on the SFTP server. Keep in mind that file paths on SFTP servers are case-sensitive.
2. Verify the runtime environment: Make sure that the Integration Runtime (IR) being used by the pipeline is set up correctly and has access to the SFTP server. If you're using a Self-hosted Integration Runtime, ensure that the machine where it's installed has access to the SFTP server as well.
3. Check file/folder permissions: Verify that the account used for SFTP authentication has the necessary permissions to access and read the file in question.
4. Inspect pipeline execution logs: Look for more detailed error messages or warnings in the pipeline execution logs. This might provide more insight into the root cause of the issue.

If none of the above suggestions resolve the issue, you can try renaming the folder and removing any special characters from the folder and file names. After renaming, update the file path in the dataset and re-run the pipeline to see if the issue is resolved.

If the issue persists even after renaming, it's recommended to contact Azure Support for further assistance, as they will have access to more detailed logs and resources to help diagnose the problem.