Search code examples
sql-servercsvssisflat-filederived-column

Handling truncation error in derived column in data flow task

I have a data flow task which contains a derived column. The derived column transforms a CSV file column, lets say A which is order number, to a data type char with length 10.

This works perfectly fine when the text file column is equal to or less than 10 characters. Of course, it throws an error when column A order number is more than 10 characters.

The column A (error prone).

12PR567890 
254W895X98 
ABC 56987K5239
485P971259 SPTGER
459745WERT

I would like to catch the error prone records and extract the order number only.

I already can configure error output from the derived column. But, this just ignores the error records and processes the others.

The expected output will process ABC 56987K5239, 485P971259 SPTGER order numbers as 56987K5239, 485P971259 respectively. The process removal of unexpected characters are not important, rather how to achieve this during the run time of the derived column (stripping and processing the data in case of error).

Solution

  • If the valid order number always starts with a number, and the length of it equal to 10. You could use Script Component (Transformation) together with Regular Expression to transform the source data.

    1. Drag and drop the Script Component as Transformation
    2. Connect the source to the Script Component
    3. From the Script Component Edit window, checked the Order from the Input columns, and make it as Read and Write
    4. In the script, add:using System.Text.RegularExpressions;

    5. The full code needs to be added in the Input process method:

      string pattern = "[0-9].{9}";
Row.Order = Regex.Match(Row.Order, pattern).Groups[1].ToString();

    6. The output going to the destination should be the matched 10 characters starting with the number.