File-Drop Automations: SFMC Pattern for Daily Imports

ot to avoid collision with other automation outputs.

/sftp/inbound/logistics/

Upstream systems must follow a predictable naming pattern. Hardcoded filenames create brittle dependencies. Instead, enforce a wildcard-compatible convention:

dispatch_YYYYMMDD.csv

This pattern allows dynamic date stamps while maintaining structural consistency. Document this convention in the integration runbook and share it with the upstream engineering team.

Step 2: Configure the File Drop Starting Source

In Automation Studio, create a new automation and select File Drop as the Starting Source. Configure the following parameters:

• SFTP Path: /sftp/inbound/logistics/
• Filename Pattern: dispatch_*.csv
• Trigger Behavior: Run immediately upon file detection
• Concurrent Runs: Disable (prevent overlapping executions)

The automation now waits for a file matching the pattern to appear. No clock schedule is required. Execution begins the moment the SFTP daemon registers the new file.

Step 3: Chain Import and Processing Activities

File drop triggers only initiate the automation. Data movement and transformation require explicit activities. The canonical chain for unencrypted CSV imports follows this sequence:

File Drop Starting Source
  → Step 1: Import File Activity
  → Step 2: Send Email Activity

Import File Activity Configuration:

• File Location: /sftp/inbound/logistics/dispatch_*.csv
• Target Data Extension: Logistics_Tracking_DE
• Update Type: Overwrite (or Add/Update depending on idempotency requirements)
• Field Mapping: Map CSV columns to DE fields using exact header matching

Send Email Activity Configuration:

• Source: Logistics_Tracking_DE
• Email Template: Tracking Notification v2
• Send Classification: Operational
• Delivery Profile: Default

Step 4: Handle Encrypted or Compressed Payloads

When upstream systems deliver PGP-encrypted or zipped files, the import chain requires an intermediate decryption step. Marketing Cloud separates file manipulation from data ingestion for security and isolation reasons.

File Drop Starting Source
  → Step 1: File Transfer Activity (Decrypt/Unzip)
  → Step 2: Import File Activity (Read from Safehouse)
  → Step 3: Send Email Activity

File Transfer Activity Configuration:

• Source File: /sftp/inbound/logistics/dispatch_*.csv.pgp
• Action: Decrypt
• Destination: Safehouse (auto-resolved by platform)
• PGP Key: Reference the configured public/private key pair

Import File Activity Configuration (Post-Decryption):

• File Location: Safehouse (platform auto-routes)
• Target Data Extension: Logistics_Tracking_DE
• Update Type: Overwrite

The Safehouse is a restricted, temporary storage area. Decrypted files never return to the public SFTP directory. This isolation prevents accidental exposure of plaintext data and enforces a clean separation between transport and ingestion layers.

Architecture Rationale

• Event-Driven Trigger: Eliminates timing assumptions. The automation executes only when data is physically available.
• Wildcard Pattern Matching: Accommodates dynamic filenames without manual updates. The * operator matches any character sequence, allowing date stamps, batch IDs, or environment tags.
• Activity Separation: File Transfer and Import File serve distinct purposes. Transfer handles transport-layer operations (decrypt, move, rename). Import handles data-layer operations (parse, map, load). Conflating them causes silent failures.
• Safehouse Isolation: Prevents plaintext data from lingering in accessible directories. Aligns with security best practices for PII and operational data.
• Idempotent Overwrites: Using Overwrite or Add/Update ensures repeated runs do not duplicate records. Pair with a primary key or batch ID for deterministic state management.

Pitfall Guide

1. Case-Sensitive Pattern Mismatch

Explanation: File Drop matching is strictly case-sensitive. A pattern defined as dispatch_*.csv will not trigger on Dispatch_*.csv or DISPATCH_*.csv. Upstream job changes that alter capitalization will cause silent skips. Fix: Enforce lowercase naming conventions in upstream scripts. Add a validation step in the integration pipeline that normalizes filenames before SFTP upload. Configure automation failure alerts to catch non-execution events.

2. Conflating File Transfer with Import File

Explanation: File Transfer manipulates files on the SFTP or in the Safehouse. Import File reads structured data and writes rows to a Data Extension. Attempting to import a .pgp or .zip file directly results in a "File Not Found" or parsing error because the platform cannot read encrypted/compressed payloads as CSV. Fix: Always place a File Transfer activity before Import File when dealing with encrypted or archived payloads. Verify the decrypted output exists in the Safehouse before chaining the import step.

3. Safehouse Lifecycle Ignorance

Explanation: The Safehouse is temporary storage. Marketing Cloud auto-purges files after 21 days. Relying on Safehouse for long-term archival or cross-automation data sharing will cause sudden failures when files expire. Fix: Treat Safehouse as a transient buffer. If downstream processes require persistent access, move decrypted files to a dedicated SFTP directory using a secondary File Transfer activity, or load data directly into a Data Extension within the same automation run.

4. Hardcoded Filename Dependencies

Explanation: Specifying an exact filename (e.g., dispatch_20240115.csv) in the File Drop configuration breaks the pipeline the moment the upstream system increments the date or changes the naming scheme. Fix: Use wildcard patterns (dispatch_*.csv). Document the pattern in the integration contract. Implement a pre-upload validation script in the upstream system to verify pattern compliance.

5. Silent Failure Blind Spots

Explanation: File Drop automations do not send native alerts when they fail to trigger or when an import returns zero rows. Teams assume success because the automation status shows "Completed." Fix: Configure automation failure notifications in Automation Studio. Set a secondary monitoring job that checks Data Extension row counts post-import. Integrate with external alerting (Slack, PagerDuty, email) using API-driven status checks or scheduled verification queries.

6. MID-Level SFTP Routing Errors

Explanation: Enterprise Marketing Cloud accounts can use a shared SFTP or per-MID (Member ID) SFTP instances. File paths, credentials, and directory structures differ between configurations. Pointing a File Drop to a shared path in a per-MID setup (or vice versa) causes "Path Not Found" errors. Fix: Verify the SFTP architecture during onboarding. Confirm whether the account uses a global or MID-scoped SFTP. Adjust path prefixes accordingly (/Import/ vs /MID_12345/Import/). Document the routing model in the infrastructure runbook.

7. Import Path Misconfiguration

Explanation: The Import File Activity defaults to /Import if no path is specified. Dropping files into /sftp/inbound/logistics/ without updating the activity configuration results in a mismatch. The automation triggers, but the import step fails silently or throws a file resolution error. Fix: Explicitly define the full SFTP path in both the File Drop Starting Source and the Import File Activity. Use relative paths consistently. Validate path alignment in a staging environment before production deployment.

Production Bundle

Action Checklist

• Define SFTP inbound directory and enforce wildcard naming convention in upstream scripts
• Configure File Drop Starting Source with exact path and pattern matching
• Chain File Transfer activity before Import File when handling encrypted or compressed payloads
• Verify Safehouse isolation and confirm 21-day purge policy aligns with data retention requirements
• Set automation failure notifications and configure secondary row-count validation
• Confirm MID-level vs shared SFTP routing and adjust path prefixes accordingly
• Document filename patterns, SFTP paths, and activity chain in the integration runbook
• Test with synthetic files in staging to validate trigger behavior, import mapping, and downstream execution

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
File arrival time varies by ±2 hours daily	File Drop Starting Source	Eliminates timing mismatch; executes only when data is available	Low (native feature, no additional licensing)
Upstream delivers PGP-encrypted CSVs	File Drop → File Transfer → Import File → Send	Decrypts in Safehouse before ingestion; maintains security isolation	Low (uses native activities; key management required)
Strict 08:00 delivery SLA with guaranteed upstream sync	Scheduled Starting Source	Predictable execution window; simpler monitoring if timing is deterministic	Low (same licensing; higher operational risk if SLA breaks)
Multi-MID enterprise with per-account SFTP	File Drop with MID-scoped paths	Prevents cross-account path collisions; aligns with routing architecture	Medium (requires path validation and MID documentation)
High-volume daily imports (>500k rows)	File Drop + Overwrite Import + Batch Send	Reduces duplicate processing; optimizes send queue allocation	Low (native optimization; monitor API limits)

Configuration Template

{
  "automationName": "Logistics_Tracking_Ingestion",
  "startingSource": {
    "type": "FileDrop",
    "sftpPath": "/sftp/inbound/logistics/",
    "filenamePattern": "dispatch_*.csv",
    "concurrentRuns": false
  },
  "activities": [
    {
      "sequence": 1,
      "type": "ImportFile",
      "name": "Import_Dispatch_Data",
      "fileLocation": "/sftp/inbound/logistics/dispatch_*.csv",
      "targetDataExtension": "Logistics_Tracking_DE",
      "updateType": "Overwrite",
      "fieldMapping": {
        "customer_email": "EmailAddress",
        "tracking_number": "TrackingID",
        "carrier": "CarrierName",
        "dispatch_date": "DispatchDate"
      }
    },
    {
      "sequence": 2,
      "type": "SendEmail",
      "name": "Send_Tracking_Notification",
      "sourceDataExtension": "Logistics_Tracking_DE",
      "emailId": 88421,
      "sendClassification": "Operational",
      "deliveryProfile": "Default"
    }
  ],
  "observability": {
    "failureAlerts": true,
    "alertRecipients": ["ops-team@company.com"],
    "postRunValidation": {
      "query": "SELECT COUNT(*) FROM Logistics_Tracking_DE WHERE _modifiedDate > DATEADD(day, -1, GETDATE())",
      "threshold": 1,
      "alertOnZero": true
    }
  }
}

Quick Start Guide

1. Create the target Data Extension: Define Logistics_Tracking_DE with fields matching your CSV headers. Set appropriate data types, null constraints, and a primary key if idempotency is required.
2. Configure the File Drop Starting Source: In Automation Studio, select File Drop, set the SFTP path to your inbound directory, and enter the wildcard pattern (dispatch_*.csv). Disable concurrent runs to prevent overlapping executions.
3. Add the Import File Activity: Chain the activity immediately after the trigger. Point it to the same SFTP path and pattern. Map CSV columns to Data Extension fields. Set update type to Overwrite or Add/Update based on your deduplication strategy.
4. Attach downstream activities: Add Send Email, SQL Query, or Data Extract steps as needed. Configure failure notifications in the automation settings.
5. Validate in staging: Upload a test file matching the pattern. Verify trigger execution, import row count, and downstream delivery. Confirm alerting works when the file is missing or malformed. Promote to production once validation passes.