SSIS Flat File Destination


The error output of the Lookup Currency Key transformation redirects any data rows that failed the lookup to the Script transformation operation. To provide more information about the errors that occurred, the Script transformation runs a script that gets each error's description.

Add and Configure a Flat File Destination

To add all the information about the failed rows to a text file for later processing and save the failed rows, we will add and configure a Flat File connection manager for the text file containing the error data and a Flat File destination.

Open the Data Flow tab, expand Other Destinations in the SSIS Toolbox.

Drag Flat File Destination

Drag Flat File Destination and place the Flat File Destination directly underneath the Get Error Description transformation.

Select the Get Error Description transformation, and then drag the blue arrow onto the new Flat File Destination.

Connect Flat File Destination

On the Data Flow design surface, rename the Flat File Destination to Failed Rows. Right-click on the Failed Rows transformation and select Edit... option.

Flat File Destination Editor

In the Flat File Destination Editor, click on the New button, and it will open the Flat File Format dialog.

Flat File Format

In the Flat File Format dialog, make sure that the Delimited option is selected, and then click the OK button. It will open the Flat File Connection Manager Editor dialog.

Enter Connection manager name

In the Flat File Connection Manager Editor, enter Error Data in the Connection manager name field and then select Browse to locate the folder to store the file.

Open File dialog

In the Open File dialog, enter ErrorOutput.txt in File name field, and then select Open.

Open File dialog

In the Flat File Connection Manager Editor dialog, verify that Locale is English (United States) and Code page is 1252 (ANSI-Latin I).

Go to the Columns page. You can see three new columns: ErrorCode, ErrorColumn, and ErrorDescription.

Open File dialog
  • These columns are the error output of the Lookup Currency Key transformation and the script in the Get Error Description transformation.
  • You can use these columns to troubleshoot the cause of the failed row.

Click on the OK button, and in the Flat File Destination Editor, clear the Overwrite data in the file check box.

Clear Overwrite data in the file checkbox

Clearing this check box persists the errors over multiple package executions by appending each new run's error output.

Go to the Mappings page to verify that all the columns are correct. Optionally, you can rename the columns in the destination.

Mappings page

Click on the OK button.

Completed mapping

Test Package

To test the package, go to the Debug menu, select Start Debugging.

Compiled successfully

In any text editor, open the ErrorOutput.txt file.

ErrorOutput.txt

All the rows in the file contain the unmatched CurrencyID value "BAD", ErrorCode value -1071607778, ErrorColumn value 0, and ErrorDescription value "Row yielded no match during lookup". The value of ErrorColumn is 0 because the error is not column-specific, rather, the lookup operation failed.