Description:

The CSV Error File step is an ETL destination component designed to handle rejected or invalid records during ETL execution.

In enterprise data integration projects, it is common for a small percentage of records to fail validation because of missing values, invalid formats, duplicate identifiers, business rule violations, or other data quality issues. Rather than terminating the entire ETL process, FlowWright can route these records to a dedicated error output for later analysis.

The step is configured by specifying:

• The output CSV file location
• An optional workflow variable or global variable to store the generated virtual file path

During execution, the ETL engine writes each rejected record to a CSV file while allowing valid records to continue through the primary ETL pipeline. This approach supports exception-based processing and simplifies operational monitoring by separating successful records from records requiring attention.

The optional Variable/Global to Hold Virtual Path property enables downstream workflow steps to reference the generated error file, making it easy to attach to notifications, archive for auditing, upload to a document management system, or initiate corrective workflows.

The CSV Error File step is typically connected to the error output of validation or transformation components.

The step supports:

• CSV error file generation
• Exception data capture
• Invalid record isolation
• Data quality reporting
• Virtual path storage
• Integration with FlowWright ETL pipelines

Typical business uses include:

• Validation error reporting
• Customer import exceptions
• Financial transaction rejection logs
• Inventory import error reports
• Data migration exception handling
• Regulatory data validation
• Enterprise data quality management

Usage:

The CSV Error File step is typically connected to the error path of ETL components that perform validation, transformation, or business rule enforcement.

During execution:

1. Incoming records are validated or processed.
2. Valid records continue through the primary ETL pipeline.
3. Rejected records are routed to the CSV Error File step.
4. Each rejected record is written to the configured CSV file.
5. If configured, the generated file's virtual path is stored in the specified workflow variable or global variable.

A typical ETL pipeline might look like this:

Typical usage scenarios include:

• Capturing customer records with missing mandatory fields
• Exporting failed financial transactions for review
• Recording invalid inventory imports
• Logging duplicate records during data migration
• Producing exception reports for business users
• Supporting data cleansing and correction processes
• Creating audit evidence for ETL quality assurance

The generated CSV error file allows business users and administrators to correct invalid records without affecting successfully processed data.

Prerequisite:

The ETL process engine service should be running to execute the ETL definitions. 

Example:

Let’s build and execute the “clsErrorCSVDef” example.          

• Create a new ETL definition called “clsErrorCSVDef”
• Drag “clsInputCSV, clsTransformFormatStringToNumber, and clsOutputCSV” steps to the canvas.
• Connect the dots between the “clsInputCSV” step and other steps, as shown above.
• Define a variable or a global variable to store the file path. 
• Click the "clsInputCSV" step to configure its "Required" properties. Provide a name for the step. Specify the path to the input CSV file on the application server. Select the ETL data schema from the drop-down list. Note: The schema's column names should match those in the input file. Click the Save button. Click here to learn about the ETL Data Schema Designer.

• Click the "clsInputCSV" step to view its "Optional" properties. Select the Show Schema button. The pop-up window displays the schema columns as shown below. This function helps to understand the schema at a glance.  

• Click the "clsValidateData" step to configure its "Required" properties. Enter the step name. Click the button to configure the expressions, then click Save. 

• Click the button to configure the expressions. The pop-up window opens for configuration. Click the Add Row (+) button to insert an empty row. Select the column and operator from the drop-down list. Enter the value to complete the expression. Select the And/Or operator to add multiple expressions. Click the Save button. You may add multiple expressions using the Add Row button. 

• Click the "clsValidateData" step to configure its "Optional" properties. Click the button to configure column types. A pop-up window appears for configuration. Click the Add Row (+) button to insert an empty row. Select the column and the data type from the drop-down list. Click the Save button. You may insert multiple column types using the Add Row button. 

• Click the "clsValidateData" step to configure its "Optional" properties. Click the button to configure the column with a regular expression. The configuration pop-up window appears. Click the Add Row (+) button to insert an empty row. Select the column from the drop-down list. Enter the regular expression. Click the Save button. Click the Add Row button to insert multiple columns. 

• Click the "clsOutputCSV" step to configure its "Required" properties. Enter a name for the step. Enter the path to the output CSV file on the application server. Click the Save button. 

• Click the "clsOutputCSV" step to configure its "Optional" properties. Provide a name for the step. Provide a variable or global reference to store the virtual output path. Click the Save button. 

• Click the "clsErrorCSV" step to configure its "Required" properties. Enter a name for the step. Enter the path to the output CSV file on the application server. Click the Save button. 

• Click the "clsErrorCSV" step to configure its "Optional" properties. Provide a name for the step. Provide a variable or global reference to store the virtual output path. Click the Save button. 

• The “Logging” configuration is necessary for documentation and also measures workflow progress and percent complete. This is achieved by configuring the step state and percent fields individually, as shown below. Configure the “Logging” using the following properties.

• Save the ETL definition, create a new instance, execute it, and then render the ETL instance. 

• Click the Items - Variables menu option to view the variable values. The variable or global contains the virtual path of the output file, as shown below. 

Tips:

• Always configure an error output when processing external data sources.
• Store the generated virtual path if error reports will be emailed or archived.
• Include sufficient validation before writing to production databases.
• Review error files regularly to identify recurring data quality issues.
• Archive historical error files to support audit and compliance requirements.
• Use descriptive file names that include execution dates or batch identifiers.
• Combine this step with Validate Data, Filter with Conditions, Transform Text Case, CSV Input File, Excel Input File, SQL Output, or CSV Output File to create complete ETL workflows with exception handling.

Notes:

• The output directory must be accessible by the FlowWright ETL server.
• The generated CSV file contains only records routed to the error path.
• The optional workflow variable or global variable stores the generated error file's virtual path for downstream workflow activities.
• File permission issues or invalid output paths may prevent successful file generation.
• Because this is an ETL destination component, it has no outgoing connections.
• ETL execution statistics, rejected record counts, and file generation errors are recorded in the ETL execution log.
• This step is commonly used for enterprise data quality management, migration projects, regulatory compliance, operational monitoring, customer onboarding, financial processing, and integration exception handling.

CSV Error File vs. CSV Output File:

Both components generate CSV files, but they serve different purposes within an ETL workflow.

As a general guideline:

• Use CSV Error File when rejected records need to be isolated for investigation and correction.
• Use CSV Output File when exporting successfully processed datasets for reporting, integration, or archival purposes.

Definition Sample:

You may download the sample ETL definition(s) from the link provided and later import them into your FlowWright ETL Designer.

Note: Verify and complete any missing configuration after importing the sample, including:

• Error file location
• Destination folder permissions
• Workflow variable or global variable (if required)
• Error routing from upstream validation or transformation steps
• Environment-specific file paths
• Downstream notification or archival workflow configuration

After verifying the configuration, save the ETL Definition before execution.

Click here to download the sample file.