Adding a Flat File Destination

  • Article

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

In this task, you will save all this information about the failed rows to a delimited file for later processing. To save the failed rows, you must add and configure a Flat File connection manager for the text file that will contain the error data and a Flat File destination to manage the actual writing of the data.

To add and configure a Flat File destination

  1. Click the Data Flow tab.

  2. In the Toolbox, expand Data Flow Destinations, and drag Flat File Destination onto the data flow design surface. Put the Flat File Destination directly underneath the Get Error Description transformation.

  3. Click the Get Error Description transformation, and then drag the green arrow onto the new Flat File Destination.

  4. On the Data Flow design surface, right-click the newly added Flat File Destination transformation, click Rename, and change the name to Failed Rows.

  5. Right-click the Failed Rows transformation, click Edit, and then click New.

  6. In the Flat File Format dialog box, verify that Delimited, is selected, then click OK.

  7. In the Flat File Connection Manager Editor, in the Connection Manager Name box type Error Data..

  8. In the Flat File Connection Manager Editor dialog box, click Browse, and locate the folder in which to store the file.

  9. In the Open dialog box, for File name, type ErrorOutput.txt, and then click Open.

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

  11. In the options pane, click Columns.

    Notice that, in addition to the columns from the source data file, three new columns are present: ErrorCode, ErrorColumn, and ErrorDescription. These columns are generated by the error output of the Lookup Currency Key transformation and by the script in the Get Error Description transformation, and can be used to troubleshoot the cause of the failed row.

  12. Click OK.

  13. (Optionail) To persist the errors over multiple package executions, clear the Overwrite data in the file check box.

  14. In the options pane, click Mappings to verify that all the columns are correct. Optionally, you can rename the columns in the destination.

  15. Click OK.

Next Steps

Testing the Lesson 5 Tutorial Package

Change History

Release History

12 December 2006
Changed content:
  • Clarified step 5 by adding a reference to the New button.
  • Added an optional step for persisting errors over multiple package executions.

17 July 2006
New content:
  • Updated steps in reflected changes in the UI.