This is my first article in the SFMC domain, and my first experience working with SSJS, so I figured I’d write it and share my experience with a larger audience who are still trying/writing their first SSJS code, as well as those who are already a SME in this area and could help me make this post more accessible to more people and improve if I’ve missed anything. So let’s get this party started!
Use Case –
So, I just received a request from a client who needed an automated notice alert sent to their email whenever a file was dropped into a File Drop Automation. File Name, Total Count, Errors, Duplicate records, Start Time, Automation Type, and Status are all details that should be provided in an email connected to a file drop.
There were also more alerts that needed to be sent in the middle of automation and at the end of automation, such as an alert of automation status if the automation was still operating for more than 1 hour. Also, if it’s completed, send an alert with the total number of records processed, as well as the duration of the start time of the records and the time it took to process them.
Solution –
We’ll only go through the first phase, which is sending an alert when a file is dropped in a file drop automation. I’m aware that this could be easily accomplished using SFMC’s default capability, namely the Import Notification Alert that we receive when a file is put into File Drop Automation.
However, we don’t have access to the file’s data, such as total records, errors, duplicates, and file name, which I’d like to include in subsequent notifications. In this situation, we may use Marketing Cloud’s development functionality in conjunction with SSJS and the WSProxy SOAP API.
We have an object ImportResultSummary through which we can get all of the above details related to the import that occurs in DE. I’ve looked into many ways to retrieve the filename from SFTP, but I believe SFMC does not provide access to get the details from the SFTP folder through any object or API. In their documentation, I couldn’t locate anything similar.
So, to solve the dependency of File Name, we could have an additional field in our file and DE, namely FileName, so that we could easily get the File Name from DE and the rest of the details through SSJS script and SQL in a separate DE, and the details of this DE could be shown as an HTML email report through AMPscript.
As a result, the file would have a “file name” field in addition to the other fields. This file name will be the same for all records that will be imported during that import. We may also provide various filenames for separate imports.
Sample File with header/fields
Implementation
As a result, we’ll begin by creating a Data Extension that will store the ImportResultSummary results as well as other DEs that will be used in our reports.
Data Extensions
- MasterDE – This DE will have the data of the subscribers to whom you need to deliver the SMS, along with the filename entered in the field “FileName.” Also, in the column “InsertTimestamp,” we have a timestamp for when the records were created in DE.
- The ImportResultSummary object data will be stored in the DE below, which will be obtained via SSJS.
- This below DE will hold the data for the report that we will send through email, it will be generated using a SQL query that joins “MasterDE” and the above DE, in this instance “POC JSON RESPONSE.”
Master DE – The newest records filename will be obtained from the master DE.
POC JSON RESPONSE – We’ll retrieve the most recent file drop import result summary details from this DE.
SSJS Script
Next, we’ll write a script to retrieve information about file imports in file drop automations. We will be using ImportResultSummary object we could retrieve-only object that contains status and aggregate information on an individual import started from an ImportDefinition.
I wrote the script using SSJS and WSProxy with the SOAP API.
<script runat="server"
Platform.Load("core", "1");
try{
var soap = new Script.Util.WSProxy();
/* Retrieve ImportDefinitions */
var cols = [
"ImportDefinitionCustomerKey",
"ImportType",
"ImportStatus",
"ID",
"ObjectID",
"NumberDuplicated",
"NumberErrors",
"NumberSuccessful",
"DestinationID",
"TaskResultID",
"StartDate"
];
var filter = {
"Property": "ImportDefinitionCustomerKey",
"SimpleOperator": "like",
"Value": "POC_PnP_TEST"
};
var response = soap.retrieve("ImportResultsSummary", cols, filter);
/* Check Retrieve status */
if (response.Status == "OK") {
var results = response.Results;
/* Loop through results */
for (i = 0; i < results.length; i++) {
var currentResult = results[i];
/* Upsert each into Data Extension */
var upsert = Platform.Function.UpsertData(
"POC_JSON_RESPONSE",
[
"ImportDefinitionCustomerKey",
"TaskResultID"
],
[
currentResult.ImportDefinitionCustomerKey,
currentResult.TaskResultID
],
[
"ImportType",
"ImportStatus",
"NumberDuplicated",
"NumberSuccessful",
"NumberErrors",
"DestinationID",
"StartDate"
],
[
currentResult.ImportType,
currentResult.ImportStatus,
currentResult.NumberDuplicated,
currentResult.NumberSuccessful,
currentResult.NumberErrors,
currentResult.DestinationID,
currentResult.StartDate
]
);
};
};
}
catch(e) {
Write(Stringify(e));
}
</script>>
Note : Because the external key we acquire through import definition, which will be created through import activity, is not the same as ImportResultSummary’s ImportDefinitionCustomerKey, I used the like operator in the above filter.
Every time a file is imported in an automation, a new ImportDefinitionCustomerKey is generated. As a result, each file import generates a unique key. So, don’t confuse ImportDefinitionCustomerKey with the External Key of the Import Activity.
We’ve also added a custom External Key in the import definition so that we can figure out which import definition details need to be fetched using the like operator in the above script.
SQL Queries
Next, we’ll write SQL queries to stitch together the data we already have.
So, except for the filename, we have all of the import definition details for the file from the preceding script. Our Master DE will provide us with the filename. So let’s join both of these DEs to acquire the most recent records from both of them, which we can then include in our report.
SELECT Top 1 a.File_Name, b.ImportStatus, b.NumberDuplicated, b.NumberSuccessful, b.NumberErrors, (b.NumberSuccessful+b.NumberErrors) as Total, max(a.INSERT_TIMESTAMP AT TIME ZONE 'Central America Standard Time' AT TIME ZONE 'UTC') as INSERT_TIMESTAMP, max(b.StartDate AT TIME ZONE 'Central America Standard Time' AT TIME ZONE 'UTC') as StartDate from [MasterDE] a INNER JOIN [POC_JSON_RESPONSE] b ON 1=1 Where Cast(a.INSERT_TIMESTAMP as Date) = Cast(b.StartDate as Date) and Cast(a.INSERT_TIMESTAMP as Date) = Cast (GetDate() as Date) and Cast(b.StartDate as Date) = Cast (GetDate() as Date) Group By File_Name,ImportStatus,NumberDuplicated,NumberSuccessful,NumberErrors ORDER By INSERT_TIMESTAMP DESC,StartDate DESC
While writing the above query, I ran upon a few roadblocks.
- There is no common field to link between the two DEs. As a result, I utilised SQL’s 1=1 syntax, which means the condition will always be true.
- I needed the most recent filename record from MasterDE and the most recent import definition record from POC JSON RESPONSE DE. Because numerous file drops may occur in a single day, the report/notification/alert is always based on the most recent record from both DEs.
- I wanted to show the Datetime in UTC, thus I changed the timezone from CT to UTC.
Email and UI Definition
Next, we’ll use AMPscript to create an email that displays the above query result in a table and sends it out as an alert to the entire group for each file drop. We built the AMPscript code below to retrieve the DE records in an email.
%%
SET @status = "Completed"
SET @rowserr = LookupRows("POC_FileReceivedDE_Data","ImportStatus",@status)
SET @rowcount= rowcount(@rowserr)
set @styletd = "font-family: 'Times New Roman',Arial, Helvetica, sans-serif; font-size: 14px; font-weight: normal; color: #00395d; line-height: 18px; mso-line-height-rule: exactly; text-align: center; padding: 5px 0;word-wrap: break-word;border:1px solid #000001;"
]%%
%%[
IF @rowcount >0 THEN
]%%
%%[ELSE]%%
<----HTML Table goes here-----!>
%%[ENDIF]%%
%%[
for @i = 1 to @rowcount do
set @rows = row(@rowserr, @i) /* get row based on counter */
set @File_Name = field(@rows,'File_Name')
set @Valid = field(@rows,'NumberSuccessful')
set @InValid = field(@rows,'NumberErrors')
set @Duplicate = field(@rows,'NumberDuplicated')
set @Total = field(@rows,'Total')
set @DateTime = field(@rows,'INSERT_TIMESTAMP')
]%%
%%[ next @i ]%% [
Use the email above in a UI definition so that we may use it in our automation.
Automation
Now we’ll group the above components/activities into an automation to automate the entire process.
- The first activity in our automation is the File Transfer activity, which unzips the file and uploads it to SFTP.
- The import activity, which will import the data into MasterDE, is the second action. In addition, as previously said, we have developed a custom External Key for this activity.
- The third activity is a script that extracts all of the information from the import file and stores it in a DE.
- The fourth activity is a SQL query that merges data from the Master DE and the script result DE and inserts it in a separate DE Send Email Activity.
- Send Email Activity.
Finally, send an email. When it reaches your inbox, it will appear like this! Please let me know how interesting or beneficial this post was for you. Please let me know if there are any changes that need to be made; recommendations are always welcome:)
