Lesson 4: Adding Error Flow Redirection

 

Applies To: SQL Server 2016 Preview

To handle errors that may occur in the transformation process, Microsoft Integration Services gives you the ability to decide on a per component and per column basis how to handle data that cannot be transformed. You can choose to ignore a failure in certain columns, redirect the entire failed row, or just fail the component. By default, all components in Integration Services are configured to fail when errors occur. Failing a component, in turn, causes the package to fail and all subsequent processing to stop.
Instead of letting failures stop package execution, it is good practice to configure and handle potential processing errors as they occur within the transformation. While you might choose to ignore failures to ensure your package runs successfully, it is often better to redirect the failed row to another processing path where the data and the error can be persisted, examined and reprocessed at a later time.
In this lesson, you will create a copy of the package that you developed in Lesson 3: Adding Logging. Working with this new package, you will create a corrupted version of one of the sample data files. The corrupted file will force a processing error to occur when you run the package.
To handle the error data, you will add and configure a Flat File destination that will write any rows that fail to locate a lookup value in the Lookup Currency Key transformation to a file.
Before the error data is written to the file, you will include a Script component that uses script to get error descriptions. You will then reconfigure the Lookup Currency Key transformation to redirect any data that could not be processed to the Script transformation.

Important
This tutorial requires the AdventureWorksDW2012 sample database. For more information about how to install and deploy AdventureWorksDW2012, Reporting Services Product Samples on CodePlex

Tasks in Lesson

This lesson contains the following tasks:

• Step 1: Copying the Lesson 3 Package
• Step 2: Creating a Corrupted File
• Step 3: Adding Error Flow Redirection
• Step 4: Adding a Flat File Destination
• Step 5: Testing the Lesson 4 Tutorial Package
  
  
  

  Step 1: Copying the Lesson 3 Package

  
  Applies To: SQL Server 2016 Preview
  
  In this task, you will create a copy of the Lesson 3.dtsx package that you created in Lesson 3. Alternatively, if you did not complete lesson 3, you can add the completed lesson 3 package that is included with the tutorial to the project, and then make a copy of it to work with. You will use this new copy throughout the rest of Lesson 4.

  To create the Lesson 4 package

  1. If SQL Server Data Tools is not already open, click Start, point to All Programs, point to Microsoft SQL Server, and then click SQL Server Data Tools.
  2. On the File menu, click Open, click Project/Solution, select SSIS Tutorial and click Open, and then double-click SSIS Tutorial.sln.
  3. In Solution Explorer, right-click Lesson 3.dtsx, and then click Copy.
  4. In Solution Explorer, right-click SSIS Packages, and then click Paste.
     By default, the copied package is named Lesson 4.dtsx.
  5. In Solution Explorer, double-click Lesson 4.dtsx to open the package.
  6. Right-click anywhere in the background of the Control Flow tab and click Properties.
  7. In the Properties window, update the Name property to Lesson 4.
  8. Click the box for the ID property, and then in the list, click <Generate New ID>.

  To add the completed Lesson 3 package

  1. Open SQL Server Data Tools (SSDT) and open the SSIS Tutorial project.
  2. In Solution Explorer, right-click SSIS Packages, and click Add Existing Package.
  3. In the Add Copy of Existing Package dialog box, in Package location, select File system.
  4. Click the browse (…) button, navigate to Lesson 3.dtsx on your machine, and then click Open.
     To download all of the lesson packages for this tutorial, do the following.
     

     1. Navigate to Integration Services Product Samples
     2. Click the DOWNLOADS tab.
     3. Click the SQL2012.Integration_Services.Create_Simple_ETL_Tutorial.Sample.zip file.
  5. Copy and paste the Lesson 3 package as described in steps 3-8 in the previous procedure.
     
     
     
     

     Step 2: Creating a Corrupted File

     Applies To: SQL Server 2016 Preview
     

     In order to demonstrate the configuration and handling of transformation errors, you will have to create a sample flat file that when processed causes a component to fail.
     In this task, you will create a copy of an existing sample flat file. You will then open the file in Notepad and edit the CurrencyID column to ensure that it will fail to produce a match during the transformations lookup. When the new file is processed, the lookup failure will cause the Currency Key Lookup transformation to fail and therefore fail the rest of the package. After you have created the corrupted sample file, you will run the package to view the package failure.

     To create a corrupted sample flat file

     1. In Notepad or any other text editor, open the Currency_VEB.txt file.
        The sample data is included with the SSIS Lesson packages. To download the sample data and the lesson packages, do the following.
        

        1. Navigate to Integration Services Product Samples.
        2. Click the DOWNLOADS tab.
        3. Click the SQL2012.Integration_Services.Create_Simple_ETL_Tutorial.Sample.zip file.
     2. Use the text editor's find and replace feature to find all instances of VEB and replace them with BAD.
     3. In the same folder as the other sample data files, save the modified file as Currency_BAD.txt.
        

        Important
        Make sure that Currency_BAD.txt is saved the same folder as the other sample data files.

     4. Close your text editor.

     To verify that an error will occur during run time

     1. On the Debug menu, click Start Debugging.
        On the third iteration of the data flow, the Lookup Currency Key transformation tries to process the Currency_BAD.txt file, and the transformation will fail. The failure of the transformation will cause the whole package to fail.
     2. On the Debug menu, click Stop Debugging.
     3. On the design surface, click the Execution Results tab.
     4. Browse through the log and verify that the following unhandled error occurred:
        [Lookup Currency Key[27]] Error: Row yielded no match during lookup.
        

        Note
        The number 27 is the ID of the component. This value is assigned when you build the data flow, and the value in your package may be different.

     Step 3: Adding Error Flow Redirection

     Applies To: SQL Server 2016 Preview
     

     As demonstrated in the previous task, the Lookup Currency Key transformation cannot generate a match when the transformation tries to process the corrupted sample flat file, which produced an error. Because the transformation uses the default settings for error output, any error causes the transformation to fail. When the transformation fails, the rest of the package also fails.
     Instead of permitting the transformation to fail, you can configure the component to redirect the failed row to another processing path by using the error output. Use of a separate error processing path lets you do a number of things. For instance, you might try to clean the data and then reprocess the failed row. Or, you might save the failed row along with additional error information for later verification and reprocessing.
     In this task, you will configure the Lookup Currency Key transformation to redirect any rows that fail to the error output. In the error branch of the data flow, these rows will be written to a file.
     By default the two extra columns in an Integration Services error output, ErrorCode and ErrorColumn, contain only numeric codes that represent an error number, and the ID of the column in which the error occurred. These numeric values may be of limited use without the corresponding error description.
     To enhance the usefulness of the error output, before the package writes the failed rows to the file, you will use a Script component to access the Integration Services API and get a description of the error.

     To 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. Place Script to the right of the Lookup Currency Key transformation.
     2. In the Select Script Component Type dialog box, click Transformation, and click OK.
     3. Click the Lookup Currency Key transformation and then drag the red arrow onto the newly added Script transformation to connect the two components.
        The red arrow represents the error output of the Lookup Currency Key transformation. By using the red arrow to connect the transformation to the Script component, you can redirect any processing errors to the Script component, which then processes the errors and sends them to the destination.
     4. In the Configure Error Output dialog box, in the Error column, select Redirect row, and then click OK.
     5. On the Data Flow design surface, click Script Component in the newly added Script Component, and change the name to Get Error Description.
     6. Double-click the Get Error Description transformation.
     7. In the Script Transformation Editor dialog box, on the Input Columns page, select the ErrorCode column.
     8. On the Inputs and Outputs page, expand Output 0, click Output Columns, and then click Add Column.
     9. In the Name property, type ErrorDescription and set the DataType property to Unicode string [DT_WSTR].
     10. On the Script page, verify that the LocaleID property is set to English (United States.
     11. Click Edit Script to open Microsoft Visual Studio Tools for Applications (VSTA). In the Input0_ProcessInputRow method, type or paste the following code.
         [Visual Basic]
         

           Row.ErrorDescription = 
             Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)
         

         [Visual C#]
         

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

         The completed subroutine will look like the following code.
         [Visual Basic]
         

         Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
         
           Row.ErrorDescription = 
             Me.ComponentMetaData.GetErrorDescription(Row.ErrorCode)
         
         End Sub
         

         [Visual C#]
         

         public override void Input0_ProcessInputRow(Input0Buffer Row)
             {
         
                 Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
         
             }
         

     12. On the Build menu, click Build Solution to build the script and save your changes, and then close VSTA.
     13. Click OK to close the Script Transformation Editor dialog box.
         
         
         

         Step 4: Adding a Flat File Destination

         Applies To: SQL Server 2016 Preview
         

         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.

         To add and configure a Flat File destination

         1. Click the Data Flow tab.
         2. 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.
         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, click Flat File Destination in the newly added Flat File Destination transformation, and change the name to Failed Rows.
         5. Right-click the Failed Rows transformation, click Edit, and then in the Flat File Destination Editor, click New.
         6. In the Flat File Format dialog box, verify that Delimited is selected, and 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. 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.
         14. 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.
         15. Click OK.
             
             

             Step 5: Testing the Lesson 4 Tutorial Package

             Applies To: SQL Server 2016 Preview
             

             At run time, the corrupted file, Currency_BAD.txt, will fail to generate a match within the Currency Key Lookup transformation. Because the error output of Currency Key Lookup has now been configured to redirect failed rows to the new Failed Rows destination, the component does not fail, and the package runs successfully. All failed error rows are written to ErrorOutput.txt.
             In this task, you will test the revised error output configuration by running the package. Upon successful package execution, you will then view the contents of the ErrorOutput.txt file.
             

             Note
             If you do not want to accumulate error rows in the ErrorOutput.txt file, you should manually delete the file content between package runs.

             Checking the Package layout

             Before you test the package you should verify that the control flow and the data flow in the Lesson 4 package contain the objects shown in the following diagrams. The control flow should be identical to the control flow in lessons 2 - 4.
             Control Flow
             

             

             Data Flow
             

             

             To run the Lesson 4 tutorial package

             1. On the Debug menu, click Start Debugging.
             2. After the package has completed running, on the Debug menu, click Stop Debugging.

             To verify the contents of the ErrorOutput.txt file

             • In Notepad or any other text editor, open the ErrorOutput.txt file. The default column order is: AverageRate, CurrencyID, CurrencyDate, EndOfDateRate, ErrorCode, ErrorColumn, ErrorDescription.
               Notice that all the rows in the file contain the unmatched CurrencyID value of BAD, the ErrorCode value of -1071607778, the ErrorColumn value of 0, and the ErrorDescription value "Row yielded no match during lookup". The value of the ErrorColumn is set to 0 because the error is not column specific. It is the lookup operation that failed. .