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.

1. Create a copy of the package DataConversion.dtsx and rename it to ErrorOutput.dtsx. Then open it.


2. Create a Flat File Connection Manager "Error Output" as shown below.



The outputs will be saved to the file C:\SSIS\ErrorOutputs.txt after the package executes.



3. Click "Advanced" tab and add 5 columns, then click OK to finish the settings on the Flat file connection manager.





4. 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.

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".



5. Click OK button. Then double-click the Flat File Destination component to open the Editor.

The connection manager is automatically assigned to the manager we just created.



6. Click Mappings tab and set the Input Columns to match Destination Columns as below.





7. Click OK button to close the editor. Then run the package and you'll get the following result.



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.



8. Open Excel Source Editor and Click "Preview..." button in the Connection Manager tab.



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.



9. 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.





10. Click OK button.

There is a warning sign in the Excel Source. We'll have to change back the setting which we did on step 4 .



11. Open the Excel Source editor, click the "Error Output" tag and change back the configuration to its default settings.





12. Click OK and run the package again.



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.