Legacy Pipeline Atlas worker-1snapshot 2026-07-15

MDClarity · task primitive

LoadDataFile

Overview

LoadDataFileTask loads delimited data files into database tables using predefined file specifications. It reads files (CSV, pipe-delimited, etc.), validates the data, loads it into the target table, and optionally archives the source file.

Use this task when: You need to import external data files into database tables using pre-configured DataFileSpec definitions.

Don't use this task when:

Parameters

Parameter Type Required Default Description
DataFileSpecKey string Yes - Key referencing a predefined DataFileSpec that defines the file structure (columns, delimiters, table name)
FilePath string Yes - Full path to the delimited file to load
ImportMode ImportMode No Replace Controls how data is loaded: Replace (drop/recreate table), Refill (truncate existing), or Append (add to existing data)
MaxInvalidRows int No 0 Maximum number of invalid rows allowed before the load fails (0 = fail on first error)
ArchiveDirectory string No null Directory to archive the file after successful load; if null or empty, file is not moved

Execution Flow

flowchart TD
    A[Start LoadDataFileTask] --> B[Retrieve DataFileSpec by Key]
    B --> C{ImportMode?}
    C -->|Replace| D[Drop and recreate table]
    C -->|Refill| E[Truncate existing table or create if not exists]
    C -->|Append| F[Create table if not exists]

    D --> G[Load delimited file into table]
    E --> G
    F --> G

    G --> H{Validation errors?}
    H -->|Errors <= MaxInvalidRows| I{ArchiveDirectory specified?}
    H -->|Errors > MaxInvalidRows| J[Throw exception]

    I -->|Yes| K[Move file to archive with timestamp]
    I -->|No| L[Leave file in place]

    K --> M[Complete]
    L --> M

    style G fill:#e1f5ff
    style K fill:#fff4e1
    style J fill:#ffe1e1

Processing Algorithm

The task uses SQL Server's bulk copy mechanism for efficient data loading:

  1. Table Preparation: Based on ImportMode, the target table is either dropped/recreated (Replace), truncated (Refill), or left as-is (Append)

  2. Bulk Load: The delimited file is loaded in batches of 5,000 rows using SQL Server bulk copy operations

  3. Validation: Each row is validated against the column specifications; invalid rows are counted and compared to MaxInvalidRows

  4. Archiving: If successful and ArchiveDirectory is provided, the file is moved to the archive directory with a timestamp suffix

Example Execution

For a file payments.txt with 10,000 rows and ImportMode = Append, MaxInvalidRows = 10:

1. Check if table "PaymentData" exists (create if needed)
2. Load rows in 5,000-row batches (2 batches total)
3. If <= 10 rows fail validation, continue; if > 10, abort entire load
4. Move payments.txt to archive/payments_2025-01-15T14-30-00.txt

Usage Examples

Example 1: Basic file load with replace

var task = new LoadDataFileTask
{
    DataFileSpecKey = "PatientDemographics",
    FilePath = @"C:\imports\patients.csv",
    ImportMode = ImportMode.Replace,
    MaxInvalidRows = 0,
    // File remains in place after load
};

Example 2: Incremental append with archiving

var task = new LoadDataFileTask
{
    DataFileSpecKey = "DailyTransactions",
    FilePath = @"C:\imports\transactions_[id].txt",
    ImportMode = ImportMode.Append,
    MaxInvalidRows = 50, // Allow up to 50 bad rows
    ArchiveDirectory = @"C:\archive\" // Move file after successful load
};

Example 3: Replace with validation tolerance

var task = new LoadDataFileTask
{
    DataFileSpecKey = "MonthlyReports",
    FilePath = @"C:\imports\january_report.psv",
    ImportMode = ImportMode.Refill, // Truncate and reload
    MaxInvalidRows = 100,
    ArchiveDirectory = @"C:\processed\"
};

Related Tasks

Task Use Case Key Difference
LoadDataFileTask Import files with known structure Requires pre-existing DataFileSpec definition
ProcessDataFileTask Import files and merge to source tables Auto-scans file structure and performs import-to-source merge workflow
ScanDataFileTask Analyze file structure Only scans file to infer columns; doesn't load data
ImportSqlTask Import from SQL queries Loads data from database queries instead of files

When to choose LoadDataFileTask:

When to choose alternatives:

Performance Considerations

Database Impact

Optimization Tips

Business Hours Considerations

Error Handling

The task uses a fail-fast approach with configurable tolerance:

// In DataStreamLoader.cs:39
IDelimitedStreamLoader delimitedStreamLoader =
    delimitedStreamLoaderFactory.Create(dataFileSpec, settings.MaxInvalidRows, 5000);
delimitedStreamLoader.LoadStream();

Behavior:

Important: The task does not aggregate errors; it tracks invalid row count and fails immediately when the threshold is exceeded. Individual row errors are logged but do not stop processing until MaxInvalidRows is breached.

Common Pitfalls

Issue Problem Solution
DataFileSpec not found Task fails with "DataFileSpec not found" error Ensure DataFileSpec with matching key exists in database before running task
File path issues Task cannot find or access file Use absolute paths; verify file permissions; check file is not locked by another process
ImportMode=Replace during business hours Users lose access to table during drop/recreate Use Refill instead, or schedule during maintenance window
ArchiveDirectory doesn't exist Task fails when trying to archive file Create directory before running task, or leave ArchiveDirectory null
MaxInvalidRows=0 too strict Load fails on first data quality issue Increase to reasonable threshold (e.g., 10-100) based on expected data quality
Wrong delimiter in DataFileSpec All rows fail validation or data loads incorrectly Verify DataFileSpec ColumnSeparator and RowTerminator match actual file format
File format mismatch Data loads but columns are misaligned Ensure file structure exactly matches DataFileSpec column definitions (count, order, types)

Code References