Counterparty File Download Setup (FTP, HTML, or Email)
This article explains how to set up a 3-job workflow to download counterparty files, review data, and bulk load into a SQL table.
What you need upfrontGather these details before creating the jobs:- Delivery method:
`FTP/SFTP`, `HTML download`, or `Email`- File location details:
- FTP/SFTP: host, port, protocol, username, auth method (password or key)- HTML: URL, authentication method (none, basic, token, cookie), any required headers
- Email: mailbox address, access method (IMAP/POP), auth credentials- File details: format (CSV, XLSX, TXT), naming pattern, delimiter, encoding, header row
- Schedule: expected delivery time, time zone, and how often files arrive
- Data mapping: target table name, column mapping, and key identifiers
- Retention: how long to keep downloaded files and processed records- Error handling preferences: notify on missing file, partial data, or schema changes
## Workflow overviewYou will set up three jobs in this order:
1.**Download job**:
fetches the file from FTP/SFTP, HTML, or email
2.**Task job**:
creates a review task so users can approve or reject the data
3. **SQL bulk load job**:
loads approved data into the target table## Job 1: Download (FTP/SFTP, HTML, or Email)Create one download job based on the delivery method:
### Option A: FTP/SFTP download-
Configure host, port, protocol, and credentials- Set the remote path and file naming pattern- Choose a local landing folder for downloads- Enable file existence checks for the expected schedule window
### Option B: HTML download-
Configure the download URL- Add authentication (basic, token, or cookie)- Configure required headers or query parameters- Set a local landing folder and expected file name### Option C: Email download- Configure mailbox access (IMAP or POP)- Filter by sender, subject, or attachment name- Choose attachment handling and a landing folder- Set a policy for duplicates (first match, newest match, or all matches)**Output:** a file stored in the landing folder for the next job.
## Job 2: Task job (Review and approve)
Create a task job that consumes the downloaded file:- Attach the downloaded file or parsed data preview- Define required reviewers and approval rules- Set up approval actions:- Approve: continue to SQL load- Reject: stop the workflow and notify owners- Include a comment field for review notes**Output:** a clear approve or reject decision with optional reviewer notes.
## Job 3:
SQL bulk loadCreate a bulk load job that only runs on approval:- Target the correct database and table- Map file columns to table columns- Configure data types, null handling, and defaults- Set up validation checks (row counts, required fields)- Enable error logging and reject handling for bad rows**Output:** validated data loaded into the target table.
## Recommended validations
- File exists and is non-empty
- Column count and header names match expectations
- Key fields are populated- Row count is within expected range
## NotificationsSet notifications for:
- Missing or late files- Review approvals and rejections- Bulk load success or failure
## Troubleshooting tips
-**FTP/SFTP:** confirm firewall access, correct port, and user permissions
-**HTML:** verify the URL in a browser, then copy headers or tokens to the job
-**Email:** test mailbox access and filters with a sample attachment
- **SQL load:** validate column order and data types before scheduling
