[TIPS] View error message of SSIS output

[TIPS] View error message of SSIS output

Reference: 1 2 

Issue:

 DataViewer on the error output shows that  "The component does not supply error code descriptions",

Solutions: 

Solution 1:

1. Enable Data Viewer: Right-click the data flow arrow line,  choose "Enable Data Viewer" or "Edit"

2.  If choose "Edit", there will be a popup, go to Data Viewer tab, check Enable data viewer.  

3. Keep the default Columns to display setting, click OK.

Solution 2:

Use a Script component to access the Integration Services API and get a description of the error, then writes these rows to a file.

Configure an error output

1. In the SSIS Toolbox, expand Common, and then drag Script Component onto the design surface of the Data Flow tab.

2. In the Script Transformation Editor dialog box, on the Input Columns page, select the ErrorCode column.

3. On the Inputs and Outputs page, expand Output 0, select Output Columns, and then select Add Column.

4. In the Name property, enter ErrorDescription and set the DataType property to Unicode string [DT_WSTR].

5. Select Edit Script to open Microsoft Visual Studio Tools for Applications (VSTA). In the Input0_ProcessInputRow method, enter or paste the following code:

C#

Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);  

The completed subroutine looks like the following code:

C#

public override void Input0_ProcessInputRow(Input0Buffer Row)  
    {  

        Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);  

    }  

On the Build menu, select Build Solution to build the script and save your changes.

Select OK to close the Script Transformation Editor dialog box.

Add and configure a Flat File destination

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

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

3. On the Data Flow design surface, select the name Flat File Destination in the new Flat File Destination transformation, and change that name to Failed Rows.

4. Right-click the Failed Rows transformation, select Edit, and then in the Flat File Destination Editor, select New.

5. In the Flat File Format dialog box, verify that Delimited is selected, and then select OK.

6. In the Flat File Connection Manager Editor, in the Connection Manager Name box enter Error Data.

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

8. In the Open dialog box, for File name, enter ErrorOutput.txt, and then select Open.

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

10. In the options pane, select Columns.

    In addition to the columns from the source data file, there are three new columns: ErrorCode, ErrorColumn, and ErrorDescription. 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.

11. Select OK.

12. 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 by appending each new run's error output.

13. In the Flat File Destination Editor, select Mappings to verify that all the columns are correct. Create mappings for any columns that are not mapped. Optionally, you can rename the columns in the destination.

14. Select OK.