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 upfront
Gather 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
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:
Option A: FTP/SFTP download
Configure host, port, protocol, and credentials
- Set the remote path and file naming pattern- Enable file existence checks for the expected schedule window- Choose a local landing folder for downloads
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
