Process Daily Files with an SSDP Custom Script Data Source

About the SSDP Custom Script Data Source

The SSDP Custom Script Data Source lets you use JavaScript to prepare data before creating a data set. In the built-in script editor, you can write custom ETL logic, such as selecting files, checking their structure, combining records, and sending the result to a data set or database. Smarten provides a library of JavaScript functions for working with dates, files, data tables, and logs.

Create a Custom Script Data Source, write and save the script there, and then create a data set from that source. You can define input variables at the data source level and supply their values when creating the data set. This lets the same script use different inputs without rewriting its logic. The exact variable names and values depend on your configuration.

For example, a script can validate daily files and combine the valid records, or read files from S3, identify duplicate records, and log them before ingestion. This article walks through the daily-file scenario. Its example logs and skips invalid files; moving rejected files to an unprocessed folder would require an additional step.

Scenario covered in this article

A source system places one CSV file per day in a month-specific folder. The Custom Script Data Source checks the run date and four previous dates, loads the files that exist, validates their columns and types, combines valid rows, and returns a data table for the data set. The example also writes that combined table to a target database and moves successfully processed files to a processed folder.

The script is a configurable example. Supply folder and database settings from your environment, confirm the filename and date formats, and test the workflow in your SSDP version before scheduling it.

Five-day processing rule

For a run dated 18 September 2024, the candidate dates are 14, 15, 16, 17, and 18 September. This means up to five files are eligible; it does not mean the process must wait until all five files exist.

Example folder and data conventions

The following path patterns are placeholders. Configure folders accessible to the SSDP runtime; do not paste a client-specific server address or credential into a published script.

The source example expects the fields below. The column names and types must match the files actually supplied to your workflow.

Check the quantity type. The supplied sample data contains fractional SalesQty values, such as 118.35. The script below therefore uses DOUBLE for that column. If your actual business rule requires whole quantities, validate the input and use INT. Confirm the date format before converting DATE to TIMESTAMP.

Before running the script, ensure the SSDP service can read the source folders, the month-specific processed folders already exist, and the target database table is ready for the selected write mode. Configure both root paths with a trailing slash so the script can append the month-year folder name.

Values to configure before running

The uppercase names in the code are placeholders. Define suitable input variables for reusable values where your Custom Script Data Source supports them, and supply their values when creating the data set. Use your approved connection or secure configuration for database credentials. Confirm how variables are referenced by scripts in your installation; copying these names without defining them will cause an error.

Configure the processing logic

• Derive the folder for each candidate date. Read the current run date, then obtain the month and year of each day in the processing window. The actual folder spelling and capitalization must match the source system.
• Check that each source folder exists. Record missing folders and skip the associated dates. If no valid files are available after these checks, the script does not write to the database.
• Build five candidate dates. Generate filenames for offsets zero through four from the run date, inclusive. Confirm that the script creates exactly five dates and uses the same date format, extension, and letter case as the delivered files.
• Check each candidate. Use a file existence check, such as FileUtils.fileExists(), before reading. Log a missing file and continue to the next candidate; an absent daily file does not by itself invalidate the other available files.
• Read and validate each available file. Load the CSV with the agreed delimiter, quote character, encoding, and record separator. The example uses DatasetUtil.createDataTableFromTextFile(). Compare the loaded columns and types with the expected schema, and exclude any file that fails validation.
• Combine valid files. Use the first valid file to create the main data table, then union each additional valid file into that table. Keep a list of the files that were successfully included so only those files can be moved after the database write.
• Write the combined data to the target table. Attempt the database write only if at least one valid file was loaded and the processed destination is available. Move the included files only after a successful write; log any file movement error.

How the custom script handles each condition

Example Custom Script

The numbered comments break the code into actions you can follow in order. Copy the entire script, then replace or bind its uppercase placeholders before running it. The comments explain the logic and do not affect execution. The filename pattern uses a numeric month; change it if your delivered files use month names.

For function signatures and parameter descriptions, see Custom JavaScript Functions in the Smarten Support Portal Knowledge Base.

The documented validateSchema method returns true when the column schema matches and false otherwise. Test both a valid and an invalid file in your installed version. Set INPUT_DATE_FORMAT to match the CSV date values used for automatic schema identification.

// Step 1: Read the run date and the configured input values.
var runDate = DateUtil.getCurrentDate();
var sourceRoot = SOURCE_ROOT_PATH;
var processedRoot = PROCESSED_ROOT_PATH;
var expectedSchema = [
["ProductCategory", "STRING"], ["Date", "TIMESTAMP"],
["SalesQty", "DOUBLE"], ["SalesPrice", "DOUBLE"]
];
var mainDataTable = null;
var validFiles = [];
var readyToWrite = true;
var preview = typeof ispreview !== "undefined" && ispreview;

// Step 2: Check today and the four preceding dates.
for (var i = 0; i < 5; i++) {
var fileDate = DateUtil.addIntervalToDate(runDate, "d", -i);
var monthYear = DateUtil.getMonthName(fileDate) + "-" +
DateUtil.getYear(fileDate, false);
var sourceFolder = sourceRoot + monthYear + "/";
var processedFolder = processedRoot + monthYear + "/";
var fileName = "test_" +
DateUtil.dateToString(fileDate, "dd_MM_yyyy") + ".csv";
var sourceFile = sourceFolder + fileName;

// Step 3: Skip missing files. A missing processed folder stops the write.
if (!FileUtils.fileExists(sourceFolder)) {
Logger.printInfoInApplicationLog("Source folder missing: " + monthYear);
continue;
}
if (!FileUtils.fileExists(sourceFile)) {
Logger.printInfoInApplicationLog("Daily file missing: " + fileName);
continue;
}
if (!FileUtils.fileExists(processedFolder)) {
Logger.printInfoInApplicationLog("Processed folder missing: " + monthYear);
readyToWrite = false;
break;
}

// Step 4: Read the CSV and reject a file if parsing or validation fails.
var dataTable = null;
try {
dataTable = DatasetUtil.createDataTableFromTextFile(
sourceFile, "true", '"', ",", "UTF-8", "true", "\n",
INPUT_DATE_FORMAT);
} catch (error) {
Logger.printInfoInApplicationLog(
"Could not read " + fileName + ": " + error.message);
continue;
}
var schemaValid = false;
try {
schemaValid = dataTable.validateSchema(expectedSchema);
} catch (error) {
Logger.printInfoInApplicationLog(
"Schema check failed for " + fileName + ": " + error.message);
}
if (schemaValid !== true) {
Logger.printInfoInApplicationLog("Excluded invalid file: " + fileName);
continue;
}

// Step 5: Combine valid data and remember its source filename.
try {
mainDataTable = mainDataTable === null ? dataTable :
mainDataTable.union(dataTable);
} catch (error) {
Logger.printInfoInApplicationLog(
"Could not combine " + fileName + ": " + error.message);
continue;
}
validFiles.push({ source: sourceFile, folder: processedFolder,
name: fileName });
Logger.printInfoInApplicationLog("Added file: " + fileName);
}

// Step 6: Write the combined table unless the run is a preview.
if (!readyToWrite) {
Logger.printInfoInApplicationLog("Run stopped: processed folder unavailable");
} else if (validFiles.length === 0) {
Logger.printInfoInApplicationLog("No valid files available for ingestion");
} else if (preview) {
Logger.printInfoInApplicationLog("Preview: database write skipped");
} else {
var writeSucceeded = false;
try {
writeSucceeded = mainDataTable.writeToDatabase(
DB_TYPE, DB_HOST, DB_PORT, DB_NAME,
DB_USER, DB_PASSWORD, TARGET_TABLE, null, DB_WRITE_MODE);
Logger.printInfoInApplicationLog(writeSucceeded ?
"Database write succeeded" : "Database write returned false");
} catch (error) {
Logger.printInfoInApplicationLog(
"Database write failed: " + error.message);
}

// Step 7: Move only files included in a successful database write.
if (writeSucceeded) {
for (var j = 0; j < validFiles.length; j++) {
try {
var moved = FileUtils.moveFile(validFiles[j].source,
validFiles[j].folder, true);
Logger.printInfoInApplicationLog((moved ?
"Processed file moved: " : "File move returned false: ") +
validFiles[j].name);
} catch (error) {
Logger.printInfoInApplicationLog(
"File move failed for " + validFiles[j].name +
": " + error.message);
}
}
}
}
// Step 8: Return the combined table to the data set.
mainDataTable;

This script checks five dates and moves files only when the database write returns true. In a preview, it returns the combined data table without writing or moving files. The final mainDataTable expression also returns the combined rows to the data set if the database write fails; the source files then remain available. If your process requires the database write to succeed before the data set receives rows, adjust that return behavior and test it in SSDP.

How to read the JavaScript

• A variable introduced with var stores a value for later use. For example, runDate holds the date on which the script runs, and mainDataTable holds the combined data.
• The for loop runs five times. The counter i takes the values 0, 1, 2, 3, and 4, which represent today and the previous four days. continue skips to the next day; break stops the loop.
• An if statement controls which action occurs. FileUtils.fileExists checks whether a candidate is available. validateSchema checks the expected columns and types. union adds another valid file to the combined data table.
• A try and catch block logs an error without hiding it. The validFiles list contains only files successfully added to the table; the script uses that list when moving files after a successful database write.

Worked example

Assume the run date is 18 September 2024. The source folder has valid files for 14, 15, 17, and 18 September; no file was delivered for 16 September. The process checks all five candidate dates and handles them as follows.

After a successful write, the four included files move to the processed folder. The missing file is logged, while the files that were present still complete the run. If the write fails, the four source files should remain available for recovery.

Configuration and verification notes

• Filename generation: the code uses dd_MM_yyyy, producing a name such as test_18_09_2024.csv. If your files are named test_18_sep_2024.csv, adjust the date formatter and verify its output with a real file.
• Schema validation: setting a flag to true without calling validateSchema does not check the file structure. Test the parsed data types with both valid and invalid sample files.
• Folder checks: confirm that FileUtils.fileExists accepts folder paths in your SSDP deployment, as the function reference describes it for files. Use the supported folder check if your version handles folders differently.
• Destination folders: the example derives both source and processed folders from each candidate date, including dates in the previous month. Create or verify the processed folders before the run.
• Retry behavior: if a database write succeeds but one of the later file moves fails, rerunning the job may load the same data again. Define the required duplicate prevention or recovery procedure for the target table.
• Database write mode: set DB_WRITE_MODE to 0 to overwrite the target table or 1 to append rows, as documented for writeToDatabase. Choose this deliberately; append mode may duplicate data on a retry, while overwrite mode replaces earlier rows.
• Connection settings: bind the DB_* placeholders to the correct target database and secure credentials in your environment. Never include a real password, server address, or client path in portal documentation.

Expected result

With the checks above in place, the SSDP custom script selects eligible files by date, ingests the available files that pass validation, and records missing or rejected files. Successful database ingestion triggers file movement; failed ingestion leaves source files available for follow-up. Review the job log and target table together to confirm which files were processed on each run.