How to do it...

  1. In the CustomLogging.dstx package, navigate to the dft_dbo_CustomLogging by double-clicking on the Data Flow task. Click on the path (blue arrow) between the ole_src_SELECT_1and the ole_dst_dbo_CustomLogging transform to select it. Right-click on it and select Delete to delete it.
  1. Drag and drop a derived column transform from the SSIS Toolbox onto the data flow. Link it to the ole_src_SELECT_1 and double-click on it to open the derived column transformation. As shown in the following screenshot, do the following:
    • Derived Column Name: DateToConvert
    • Derived Column: leave it as <Add as new column>
    • Expression: Type 1600-01-00. This is not a valid date; it will cause an error and that's precisely what we want.
  1. Click on OK when finished.
  1. Rename the derived column der_AddDate.
  2. Now, drag and drop a data conversion transform onto the dataflow task. Attach it to the der_AddDate derived column created previously and double-click on it to open the Data Conversion Transformation Editor. Enter the values as shown in the following screenshot:
    • Input Column: Check the column DateToConvert in the Available Input Columns.
    • Output Alias: Change it to DateConverted.
    • Data Type: Select date [DT_DATE] from the drop-down list.

It is also shown in the following screenshot:


  1. The Data Conversion Transformation Editor should now look like the following screenshot. Click on the Configure Error Output... button.
  1. You will get a screen like the following screenshot. By default, the Error and Truncation errors will fail the component.
    1. Select both columns and from the drop-down list near Set this value to select cells, select Redirect row. Click Apply.
    2. You should now see that both column values are now set to Redirect row.
    1. Click OK to close the editor and rename the transform as dcnv_DateConverted.
  1. Now, bring an audit transform from other transforms onto the dataflow task. The following steps detail what's in the following screenshot:
    1. Attach the Data Conversion Error Output (red path or arrow) to it.
    2. Right-click on the error path and select Enable Data Viewer from the menu that appears.
As we did before in the Customized logging level recipe, select all the transforms and click on Make Same Width from the Layout toolbar. From the Format menu, select Format Auto Layout à Diagram to format the data flow task objects properly.
  1. Your data flow task should look like the following screenshot. Now, right-click anywhere in the background of the data flow task and select Execute Task from the menu.

  1. You should see a data viewer like the following screenshot:

It is explain in as follows:

  • The DateToConvert is the column we tried to convert.
  • The second column is the ErrorCode, an internal code to SSIS.
  • The third column is the ErrorColumn which is a lineage ID that SSIS assigns to all columns in the data flow task.
  • The ErrorCode - Description column gives us the reason for the error.
  • The ErrorColumn - Description gives us the column that failed to be converted (DateConverted).
上一章目录下一章