Converting RTF to Plain text in SSIS

RichText formatted data can easily be converted to plain text using a regular expression in the script component in SSIS.

The regular expression to strip out rtf formatting is as follows:

({\\)(.+?)(})|(\\)(.+?)(\b)

The following steps outline the creation of an SSIS package to extract records from a database table to a delimited text file where the data in one of the columns is formatted as RTF and the output file requires plain text.

1. Create a new SSIS data flow
   
   
2. Add an OLE DB Source to access the records. The column containing the rtf data is assumed to be called body.
   
   
3. Add a script component to the data flow and attach it to the output of the OLE DB Source.
   
   
4. In the script component properties ensure the body field is selected as an input column
   
   
5. In the script component properties add an output column body_converted, give it a datatype of DT_STR and a suitable length.
   
   
6. Click the Edit Script button and then use the following code in the ProcessInputRow method.
   
   
   
   Regex regExObj = new Regex("({\\\\)(.+?)(})|(\\\\)(.+?)(\\b)");
   System.Text.UTF8Encoding enc = new System.Text.UTF8Encoding();
   String strBody = enc.GetString(Row.body.GetBlobData(0,int)Row.body.Length));
   Row.bodyconverted = regExObj.Replace(strBody, "").Replace("}", "");
   
   
   
7. Add aflat file destination and map the columns from the script component to it ensuring you use the body_converted column and not the original body column.
   
   
8. Run the SSIS pacakge.

You should now have a file which contains records with plain text instead of rtf.

The regular expression works on all scenarios I have tested so far, but if you come across tags where it misses then it should be a simple case of extending the expression to catch them.