Files Stored in respective folder

Question

Hello Team,

There are multiple files stored in 3 folder in SFTP Location.The pipeline is schduled for every 2 hours.

My queries is if any news files is not present on the schdule time then the pipeline must not run. If any new files are present in the respective 3 folder then the pipeline must run.

I have designed the pipeline for similar scenario.But it is not working .

the GetmetaData Activity is mapped to each folder and is mentioned as

Start time : @formatDateTime(addMinutes(pipeline().TriggerTime, -5), 'yyyy-MM-ddTHH:mm:ssZ')

End time : @formatDateTime(pipeline().TriggerTime, 'yyyy-MM-ddTHH:mm:ssZ')

Filed List : Child itmes, Last Modified

Filter Activity:

Items : @activity('Check_Full_Folder').output.childItems

condition: @contains(item().name, formatDateTime(utcNow(),'MMddyyyy'))

Filter Activity:

Items : @activity('Full_Folder').output.value

condition:@greater(ticks(item().lastModified),ticks(addMinutes(utcNow(), -5)))

If condition:

@and(greater(length(coalesce(activity('Full_Folder_UpdatedFiles').output.value, [])), 0),

and(greater(length(coalesce(activity('Custom_Folder_UpdatedFiles').output.value, [])), 0),

greater(length(coalesce(activity('Parent_Folder_UpdatedFiles').output.value, [])), 0)

)

)

When ever the pipeline run manually i am getting Bad request :

{ "code": "BadRequest", "message": null, "target": "pipeline//runid/facc0cf1-d160-4440-b5b4-c4a8e7df1550", "details": null, "error": null }

Please advise,whenver the new files present in the respective folder the pipeline must run succesfully other wise pipeline must not run.

Please advise.

Regards

Rohit

Answer 1

Hello Rohit !

Thank you for posting on Microsoft Learn Q&A.

pipeline().TriggerTime only has a value when the pipeline is started by a trigger and when you run the pipeline manually pipeline().TriggerTime is null, so formatDateTime() / addMinutes() blow up and ADF wraps that as a generic BadRequest.

You can use utcNow() when there is no trigger time:

@formatDateTime(
    addMinutes(coalesce(pipeline().TriggerTime, utcNow()), -5),
    'yyyy-MM-ddTHH:mm:ssZ'
)

and

@formatDateTime(
    coalesce(pipeline().TriggerTime, utcNow()),
    'yyyy-MM-ddTHH:mm:ssZ'
)

So your start time” and end time become:

• Start time

    @formatDateTime(
        addMinutes(coalesce(pipeline().TriggerTime, utcNow()), -5),
        'yyyy-MM-ddTHH:mm:ssZ'
    )
  

• End time

    @formatDateTime(
        coalesce(pipeline().TriggerTime, utcNow()),
        'yyyy-MM-ddTHH:mm:ssZ'
    )
  

When the pipeline is started by the schedule trigger, pipeline().TriggerTime is used and when you run manually, pipeline().TriggerTime is nullthe coalesce() uses utcNow() instead.

For the filter 1, that’s OK if filenames always contain today’s date. If you want it aligned with the trigger window instead of now, you could also use coalesce(pipeline().TriggerTime, utcNow()) here.

Items:     @activity('Check_Full_Folder').output.childItems
Condition: @contains(item().name, formatDateTime(utcNow(), 'MMddyyyy'))

For the 2nd filter, do the same for the custom & parent folders.

Items:     @activity('Full_Folder').output.value
Condition: @greater(
              ticks(item().lastModified),
              ticks(addMinutes(coalesce(pipeline().TriggerTime, utcNow()), -5))
           )

If condition only run when all 3 folders have new file, you can run only if each of the three arrays has at least one element and if you actually want “run if any folder has a new file”, replace the outer and with or

@and(
    greater(length(coalesce(activity('Full_Folder_UpdatedFiles').output.value, [])), 0),
    and(
        greater(length(coalesce(activity('Custom_Folder_UpdatedFiles').output.value, [])), 0),
        greater(length(coalesce(activity('Parent_Folder_UpdatedFiles').output.value, [])), 0)
    )
)

Inside the true branch you keep your real work and in the False branch you leave it empty or maybe log no new files.

Answer 2

Hi Rohit Kulkarni,
Thankyou for Reaching Microsoft Q&A!
it sounds like you're trying to manage a scheduled pipeline in Azure Data Factory that should only run if there are new files present in your SFTP folders. It looks like you've set up a comparison using the GetMetadata and Filter activities, but you're encountering a "Bad Request" error when running the pipeline.

Here's a breakdown of what you can do to troubleshoot and achieve your goal:

1. Check Your Logic in Conditions:
   • Ensure that your conditions for checking updated files are correctly set up. The logic seems a bit complex, so break it down to verify if each part operates as expected.
   • For example, check if the greater(length(...), 0) conditions are being satisfied during execution.
2. Validate Filter Expressions:
   • Review the expressions used in your Filter activities. If there's a typo or the logic isn't quite right, it might prevent the pipeline from executing properly. Test each filter individually if possible.
3. Inspect Trigger Time:
   • Confirm that the TriggerTime is working as expected. Make sure the time zone settings are consistent and that you're correctly referencing the time in your date formatting.
4. Review Parameters:
   • Ensure that the Items, Field List, and conditions used in your activities are being passed correctly and are returning expected values.
5. Use Debugging Mode:
   • Make use of the debugging capabilities in Azure Data Factory. Running the pipeline in debug mode allows you to see intermediate output and trace where the failure might be occurring.
6. Handle the Bad Request Error:
   • The "Bad Request" error often indicates an issue with the configuration or the way parameters are being provided. Check the pipeline's parameters and make sure they match what's expected for each activity.

If the challenges persist, you might also want to simplify your pipeline just to get a clear output of what's happening. Start small, maybe just checking one folder, and then incrementally add complexity back in until you find where it breaks.

Here are some follow-up questions that could help clarify the situation further:

• What specifically do you see inside the output of the GetMetadata activities when you run the pipeline manually?
• Are there any specific error logs or codes beyond the "Bad Request" error message?
• Could you isolate and test individual components or activities in the pipeline to see if they function correctly on their own?

Hope this helps! Feel free to share any more details if you need further assistance.

References:

• Copy data from or to Azure Files by using Azure Data Factory
• Delete activity in Azure Data Factory – Limitations
• Optimizing Data Copy Performance in Azure Data Factory