I would find a way to identify all file names in that FTP path folder then store the file names in a DE. Hence whenever I send an email, I would make a lookup into that DE to check if the file exists in the FTP folder or not. If not, I won't attach the file and do something else.
Step 1:
Create a DE to store the file names in the FTP folder. For example, this DE below has 1 field:
DE name: File Name List
Field name: File, type: text, length = 100
Step 2: Identify all file names in the FTP folder.
You can use this undocumented endpoint to get all file names
Host: https://YOUR_SUBDOMAIN.rest.marketingcloudapis.com
GET /legacy/v1/beta/automations/filecollection/?nofilter=true&foldername=import%2Frun%2F20230430
Content-Type: application/json
Authorization: Bearer YOUR_ACCESS_TOKEN
If you notice to the endpoint URL, you will see this: import%2Frun%2F20230430
This implies for the FTP folder path:
import/run/20230430
I put an example response here so you can visualize the JSON response construction
{
"startIndex": 0,
"itemsPerPage": 50,
"totalResults": 2,
"entry": [
{
"directory": {
"parentFolder": "import/run/20230430",
"childFolders": [
{
"hasChildren": false
}
],
"files": [
{
"fileName": "cts_to_all_sub.csv",
"fileSize": 1898
},
{
"fileName": "dcle_test.csv",
"fileSize": 1898
}
]
}
}
]
}
Coding, you can write a script activity for this or put it in a cloudpage:
<script runat="server">
Platform.Load("Core","1.1.1");
var FTPfiles = DataExtension.Init("File Name List");
var lst = [];
var url = 'https://YOUR_SUBDOMAIN.rest.marketingcloudapis.com/legacy/v1/beta/automations/filecollection/?nofilter=true&foldername=import%2Frun%2F20230430';
var req = new Script.Util.HttpRequest(url);
req.emptyContentHandling = 0;
req.retries = 2;
req.continueOnError = true;
req.contentType = 'application/json';
var auth = 'Bearer ' + YOUR_V2_Access_Token;
req.setHeader("Authorization", auth);
req.method = 'GET';
var resp = req.send();
var resultJSON = Platform.Function.ParseJSON(String(resp.content));
if (resultJSON.entry.length > 0) {
var fileNums = resultJSON.entry[0].directory.files.length; // number of files in the folder
if (fileNums > 0 ) {
for (var i=0; i < fileNums; i++) {
var FTPfileName = resultJSON.entry[0].directory.files[i].fileName;
// Write(FTPfileName + "<br>"); debug
var fileNameJSON = {"File": FTPfileName};
lst.push(fileNameJSON);
}
FTPfiles.Rows.Add(lst); // Store the file names into the "File Name List" DE
}
}
Step 3: check if the file name exists in the "File Name List" DE or not
It's actually an easy part now. In your email, put a lookup to check the file name in the File Name List DE. If it doesn't exist, you can put what you want to handle exception after that.
%%[
set @fn = AttributeValue('FileName')
set @FTPfile = LookupRows("File Name List", "File", @fn)
set @rows = RowCount(@FTPfile)
if @rows > 0 then
AttachFile("FTP", concat("\Run\20230430\",@fn))
else
/* Put what you want to handle the exception here*/
RaiseError("No attachment file is available for this customer.")
endif
]%%
I hope this works for you!