SSIS Data Flow Error Output
Error outputs are the output stream of a data flow component whenever an error occurs on the component. It is well supported by the most components and 2 columns Errorcode and ErrorColumn are added automatically in the output.
In the following example, we'll add 2 records with wrong date format and wrong number in the source Excel file and save the error outputs to a flat file.
Let's add 2 wrong records in the Excel file C:\SSIS\Books.xlsx first.
Then start to create the example by the following step-by-step.
- Create a copy of the package DataConversion.dtsx and rename it to ErrorOutput.dtsx. Then open it.
- Create a Flat File Connection Manager "Error Output" as shown below.
- Click "Advanced" tab and add 5 columns, then click OK to finish the settings on the Flat file connection manager.
- Click Data Flow tab and drag & drop Flat File Destination into the data flow edit panel and link the Error Outputs (Red Arrow) of the Excel Source to it.
- Click OK button. Then double-click the Flat File Destination component to open the Editor.
- Click Mappings tab and set the Input Columns to match Destination Columns as below.
- Click OK button to close the editor. Then run the package and you'll get the following result.
- Open Excel Source Editor and Click "Preview..." button in the Connection Manager tab.
- Remove the Error Output from Excel Source and put the error output of OLE DB Destination to Flat File Destination. And change the Error column to "Redirect row" in the pop-up dialogue.
- Click OK button.
- Open the Excel Source editor, click the "Error Output" tag and change back the configuration to its default settings.
- Click OK and run the package again.
The outputs will be saved to the file C:\SSIS\ErrorOutputs.txt after the package executes.
The Configure Error Output dialogue box will be popped up.
The Error column saves the action when an error occurs and the Truncation column saves the action when a truncation occurs, for example, trying to save a string with 20 length to a field with 10 length. The table below will explain the 3 action options of which you will have to choose one.
Option | Explanation | Default Option |
---|---|---|
Fail Component | The whole Data Flow task fails when an error or a truncation occurs. | Yes |
Ignore Failure | Ignore the error or the truncation and direct the row to the normal output. | No |
Redirect Row | Direct the row to the error output when an error or a truncation occurs. | No |
In this example, we just change the Error column of Price and Bought Date row to "Redirect row".
The connection manager is automatically assigned to the manager we just created.
This result is not the expected one. What's happened? Why was the Excel Source returned nothing in Error Outputs?
Based on the Microsoft explanation, the Excel driver gets the first several rows to evaluate the data type of each column. By default, they are the first 8 rows of the Excel file. Once the driver decides in favour of the majority data type, the cells with the other types will be regarded as NULL value when the data is being loaded.
In this case, we'll check the data in Excel Source.
As you can see, the cells with invalid data were changed to NULL values. There is no records going to Error Outputs.
Let's capture the destination error outputs and see what will be happened.
There is a warning sign in the Excel Source. We'll have to change back the setting which we did on step 4 .
Only 1 record went to Error Outputs. Why?
Here is the reason.
Another record with NULL value in the BoughtDate column was already added in the books table because NULL is allowed in BoughtDate.