In B2B e-commerce implementations, managing product catalog assets at scale is a recurring challenge. Clients frequently provide product-to-image mappings through spreadsheets stored in the platform's userFiles directory or cloud storage. Standard import jobs often require rigid file formats or predefined database staging processes, creating a gap when flexible spreadsheet parsing needs to connect directly to product media entities.

To address this challenge, I designed and implemented a custom two-stage integration job in Optimizely Configured Commerce. The solution consists of:

• ImportProductImagesProcessor: A custom IIntegrationProcessor based on the out-of-the-box IntegrationProcessorFlatFile class that dynamically reads and parses Excel files from the IUserFilesStorageProvider.
• JobPostprocessorAssignProductImages: A custom IJobPostprocessor that processes the parsed dataset and assigns image paths to Product entities in the database using the Unit of Work pattern.

This article examines the integration architecture, code structure, and implementation best practices.

Architectural Overview

The integration follows Optimizely's Internal pipeline model:

[ Excel File in userFiles ]
             |
             v
+-------------------------------------------------+
| ImportProductImagesProcessor                    |
| (Reads stream, applies SELECT/WHERE in-memory)  |
+-------------------------------------------------+
             |
             v  (In-Memory DataSet)
+-------------------------------------------------+
| JobPostprocessorAssignProductImages             |
| (Queries DB, updates/creates image entities)    |
+-------------------------------------------------+
             |
             v
[ Database: Product & ProductImage Entities ]

Stage 1: Reading and Parsing the Excel File (ImportProductImagesProcessor)

The processor handles file discovery, stream retrieval, parsing through GemBox, and in-memory filtering.

Key Technical Highlights

1. Decoupled File Storage Access via IUserFilesStorageProvider

Rather than relying on direct disk I/O (System.IO.File), the processor uses Optimizely's IUserFilesStorageProvider. This abstraction supports files stored locally, on network shares, or in cloud storage such as Azure Blob Storage.

private Stream GetFile(string fileName) =>
    userFileStorageProvider.GetFile(fileName).OpenRead();

2. Pattern Matching and Dynamic Path Resolution

The GetFiles method resolves wildcard search patterns configured in the Integration Job Step's FromClause, such as Catalog/Images/*.xlsx. It converts wildcards into regular expressions to identify matching files:

private static Regex BuildPatternRegex(string filePattern) =>
    new(
        "^" + Regex.Escape(filePattern).Replace(@"\*", ".*").Replace(@"\?", ".") + "$",
        RegexOptions.IgnoreCase | RegexOptions.Compiled
    );

3. Custom File Parsing and Integration Step Clause Evaluation

Using FileParserGemBox, the Excel stream is converted into a standard .NET DataTable. The processor then applies standard Optimizely JobDefinitionStep filter clauses in memory:

• SELECT Clause Filtering: Retains only the requested columns, such as ProductID and ImageName.
• WHERE Clause Filtering: Filters rows dynamically based on the job step definition, such as [ImageName] IS NOT NULL.

dataTable = ParseFile(fileName);
dataTable = ApplySelectClauseToDataTable(jobDefinitionStep, dataTable);
dataTable = ApplyWhereClauseToDataTable(jobDefinitionStep, dataTable);

Stage 2: Mapping Images to Products (JobPostprocessorAssignProductImages)

Once the file is parsed into a DataSet, the post-processor updates the corresponding database entities.

Key Technical Highlights

1. Batch Entity Verification

To avoid N+1 database queries, the post-processor extracts all product numbers from the parsed DataSet, builds a hash set, and performs a single AsNoTracking query against the Product repository to verify which products exist:

var productNumbers = productImageList
    .Select(x => x.ProductNumber)
    .ToHashSet(StringComparer.OrdinalIgnoreCase);
var existingProducts = productRepo
    .GetTableAsNoTracking()
    .Where(p => productNumbers.Contains(p.ErpNumber))
    .Select(p => p.ErpNumber)
    .ToList();

2. Upsert Logic with Specific Image Sizing

For each matching product, the processor checks whether a ProductImage record exists for the specified SortOrder:

• Insert: If no ProductImage exists at SortOrder = 1, a new entity is created and added to the product's ProductImages collection with ImageType = "Static".
• Update: If the record already exists, the processor reads the ImageSize job parameter (small, medium, or large) and updates only the corresponding image path.

var productImage = product.ProductImages.FirstOrDefault(x => x.SortOrder == imageInfo.SortOrder);
if (productImage == null)
{
    product.ProductImages.Add(new ProductImage
    {
        ImageType = "Static",
        SmallImagePath = imageInfo.SmallImage,
        MediumImagePath = imageInfo.MediumImage,
        LargeImagePath = imageInfo.LargeImage,
        SortOrder = imageInfo.SortOrder
    });
    RecordsAdded++;
    return;
}
// Update existing image path based on ImageSize parameter
switch (imageSize?.ToLowerInvariant())
{
    case "small":
        productImage.SmallImagePath = imageInfo.SmallImage;
        break;
    case "medium":
        productImage.MediumImagePath = imageInfo.MediumImage;
        break;
    case "large":
        productImage.LargeImagePath = imageInfo.LargeImage;
        break;
}
RecordsModified++;

3. Unit of Work Management and Metrics Reporting

The processor uses IUnitOfWork for transactional consistency, calling _unitOfWork.Save() at the end of batch execution. In the finally block, it releases resources through _unitOfWork.Clear() and assigns audit counts (RecordsAdded and RecordsModified) to the IntegrationJob instance for logging in the Admin UI.

Software Architecture Recommendations and Improvements

While the implementation achieves its primary objective, the following enhancements can improve performance and flexibility for higher-volume workloads:

1. Batching Database Writes: For files containing tens of thousands of rows, calling _unitOfWork.Save() only at the end can consume significant memory or cause database timeouts. Processing and saving records in smaller batches, such as 500 to 1,000 records, can help optimize performance.
2. Support for Multiple Sort Orders: The current implementation hardcodes SortOrder = 1 in GetProductImageInfoList. Extending ProductImageInfo to map an explicit SortOrder column from Excel would support secondary and gallery image management.

Combining custom IIntegrationProcessor and IJobPostprocessor implementations provides a flexible way to map product images from non-standard input files, including Excel spreadsheets stored in cloud media folders, directly to Optimizely Configured Commerce domain models.

This approach uses built-in platform abstractions, including IUserFilesStorageProvider and IUnitOfWork, to maintain consistency with Optimizely's architecture while supporting custom file parsing and entity mapping requirements.