How to check if file exists

Question

How to check if file exists

bk 466

Hi All,
I have a copy job that fails when the file doesn't exist on the SFTP Server.
Current scenario :
Source SFTP Server
Target is ADLS Gen2
The pipeline runs fine when a file exits , but fails when there is no file , which is expected. How to overcome this issue? In SSIS there is a script task which checks if file exists or not , if file exists it executes the package else not.
Can someone please guide me in the right direction?

Thanks

3 answers

Your answer

Answer 1

MartinJaffer-MSFT 26,161

Hello @bk and thank you for your question.

The Validation activity can be used to check whether a file exists or not. It can also be used to wait until a file exists, or is a certain size.

In the validation activity, you specify several things. The dataset you want to validate the existence of, sleep how long you want to wait between retries, and timeout how long it should try before giving up and timing out. The minimum size is optional.

Be sure to set the timeout value properly. The default is 7 days, much too long for most jobs.

If the file is found, the activity reports success.
If the file is not found, or is smaller than minumum size, then it can timeout, which is treated like a failure by dependencies.

Please let me know if your question was actually "How do I make my pipeline not report failure when file is missing?"

bk 466 Reputation points

2020-09-21T18:30:44.737+00:00

Thanks Martin, i am not sure i quiet understood that. All i want is a process to check if a file "random.csv" exists or not, if not do nothing , if yes run the COPY Activity. When i added validation Activity i provided the same dataset as the SOURCE for COPY since the file is the same, it does nothing.
MartinJaffer-MSFT 26,161 Reputation points

2020-09-21T19:05:36.977+00:00

Thank you for responding @bk .
A simple process to do that would look like the first picture below. When the validation activity finds the file, its success leads to the Copy activity. When the validation activity does not find the file, it does nothing. The "Do nothing" wait activity is there purely for show, and can be omitted.

You said:

When i added validation Activity i provided the same dataset as the SOURCE for COPY since the file is the same, it does nothing.

I am a little confused. Isn't nothing what you wanted to happen?
Luthra, Pankaj 1 Reputation point

2022-06-06T14:27:15.937+00:00

Hi Martin,

is there anyway to prevent the pipeline from not failing if a file does not exist. I have setup my pipeline as you advised but after timeout it fails.

thanks.
MartinJaffer-MSFT 26,161 Reputation points

2022-06-15T19:54:33.263+00:00

Hello @Luthra, Pankaj . Here is a solution, if you want neither case to be a pipeline failure. See image below.

A pipeline fails when there is a success path, AND that path is not taken.

By using a blue on-completion dependency to link checkIfFileExists and MarkFileAsLoading, instead of a success dependency, it is no longer a success path. It will run in either success of fail of checkIfFileExists. So, we can use an skipped-dependency to link MarkFileAsNotAvailable to MarkFileAsLoading.

When an activity has 2 incoming dependencies from different activities, both conditions must be fulfilled before it runs. Therefore MarkFileAsLoading will run only when BOTH checkifFileExists completes, AND MarkFileAsNotAvailable does NOT run.

MarkFileAsNotAvailable does not run, when checkIfFileExists does not fail, in other words, succeeds. Ergo, we have a success-like condition without using the green on-success dependency.

Answer 2

bk 466

Thanks Again, please see the screen shot, is that correct?

MartinJaffer-MSFT 26,161 Reputation points

2020-09-21T20:39:58.683+00:00

Yes, you chained the activities correctly @bk .

Timed out means the files were not found, and so the copy did not happen. Is this not the desired behavior?

If the file is found, then the copy activity should run.

Is validation not finding the file even when you see with your own eyes it does exist?

Answer 3

Bill Bird 81

The validation doesn't work like you think it does...I had trouble too. The last activity is waiting to hear from ALL the activities. Hopefully this picture will help.

MartinJaffer-MSFT 26,161 Reputation points

2020-09-24T19:46:13.077+00:00

What you say is true, @Bill Bird , but I am lost on how you arrived at this from bk-4602 's question.

@bk , did Billbird-9016 's answer help you? If so please mark it as accepted answer.

Please let us know if you still need help.
Bill Bird 81 Reputation points

2020-09-24T23:55:08.44+00:00

@MartinJaffer-MSFT @bk has a screenshot showing his pipelines timing out AND his screenshot also shows that he was only moving to the next activity upon a successful validation....It happened to me too so I drew a picture showing one of my pipelines that had a validation activity in it. I found it useful to draw it out, so I just shared my notes. :)
MartinJaffer-MSFT 26,161 Reputation points

2020-09-25T19:39:47.45+00:00

I thought that was what he wanted to have happen. If validation finds file, do copy. Otherwise abort. I feel like I am not seeing something obvious.

Anyway, thank you for sharing @Bill Bird . I wish there were more members like you. Please feel free to post on any issue, even if a MSFT has replied. One of our goals is to grow the community to where users answer each others questions.

Share via

How to check if file exists

3 answers

Your answer