Step 4: Adding a Flat File Destination
Applies To: SQL Server 2016
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. By setting properties on the Flat File connection manager that the Flat File destination uses, you can specify how the Flat File destination formats and writes the text file. For more information, see Flat File Connection Manager and Flat File Destination.
Click the Data Flow tab.
In the SSIS Toolbox, expand Other, and drag Flat File Destination onto the data flow design surface. Put the Flat File Destination directly underneath the Get Error Description transformation.
Click the Get Error Description transformation, and then drag the green arrow onto the new Flat File Destination.
On the Data Flow design surface, click Flat File Destination in the newly added Flat File Destination transformation, and change the name to Failed Rows.
Right-click the Failed Rows transformation, click Edit, and then in the Flat File Destination Editor, click New.
In the Flat File Format dialog box, verify that Delimited is selected, and then click OK.
In the Flat File Connection Manager Editor, in the Connection Manager Name box type Error Data.
In the Flat File Connection Manager Editor dialog box, click Browse, and locate the folder in which to store the file.
In the Open dialog box, for File name, type ErrorOutput.txt, and then click Open.
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).
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.
In the Flat File Destination Editor, clear the Overwrite data in the file check box.
Clearing this check box persists the errors over multiple package executions.
In the Flat File Destination Editor, click Mappings to verify that all the columns are correct. Optionally, you can rename the columns in the destination.