Marketing Cloud - AttachFile exception handling

Question

We have a custom data extension with an attribute named "FileName" which houses the actual file name.

Currently we use the following code to attach a file from our marketing cloud FTP for use in our single send email journeys.

%%[ 
var @fn 
set @fn = AttributeValue('FileName') 
AttachFile("FTP", concat("\Run\20230430\",@fn)) 
]

Is there a way to handle exceptions when we try to attach a file using AMPScript's AttachFile function ?

I found that we can use RaiseError to log an exception during send processing but AttachFile function does not return any output then I am not sure how we will be able to use RaiseError (or any other way to handle exception scenarios like file not found etc)

https://ampscript.guide/raiseerror/

https://ampscript.guide/attachfile/

Answer 1

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!