Removing RTF value when importing from share point list to SQL table


  • Hi,

    I need to import share point list values in sql table. I have made SSIS package to do that and it is working fine.

    But I can see rich text also in my sql tables. To find out how to get rid of them I found below link (and it also works great. I tried a sample). For some reason I can not post link so below is the code:

    Option Explicit
    Function stripHTML(strHTML)
    'Strips the HTML tags from strHTML
      Dim objRegExp, strOutput
      Set objRegExp = New Regexp
      objRegExp.IgnoreCase = True
      objRegExp.Global = True
      objRegExp.Pattern = "<(.|\n)+?>"
      'Replace all HTML tag matches with the empty string
      strOutput = objRegExp.Replace(strHTML, "")
      'Replace all < and > with &lt; and &gt;
      strOutput = Replace(strOutput, "<", "&lt;")
      strOutput = Replace(strOutput, ">", "&gt;")
      stripHTML = strOutput    'Return the value of strOutput
      Set objRegExp = Nothing
    End Function
    <form method="post" id=form1 name=form1>
      <b>Enter an HTML String:</b><br>
      <textarea name="txtHTML" cols="50" rows="8" wrap="virtual"><%=Request("txtHTML")%></textarea>
      <input type="submit" value="Strip HTML Tags!" id=submit1 name=submit1>
    <% if Len(Request("txtHTML")) > 0 then %>
    	<b><u>View of string <i>with no</i> HTML stripping:</u></b><br>
    	<b><u>View of string <i>with</i> HTML stripping:</u></b><br>
    <% End If %>

    I have taken this from

    Now my question is how could I use this code in SQL Or SSIS package. I am fairly new to SQL world so if you can pl guide me step by step that would be great help.

    Thanks a lot in advance.


    Sunday, March 02, 2014 6:10 AM


  • 1. Create a object variable in SSIS to store table column result in it.

    2. Drag a Execute SQL task. Give your query for getting column values from table which will look like below

    SELECT Column FROM table

    Set ResultSet option as  Full Resultset and map object variable to index 0

    3. Add a FOrEachLoop based on ADO enumerator and map object variable inside it. Then inside add another string variable to get individual values

    4. Inside Loop Add Script task and pass the variable in ReadWrite mode inside

    5. Include above function in script task and Pass the variable value to it. get return value back and assign it back to variable.

    6. Finally use the variable in whatever other tasks you want to get output.

    Please Mark This As Answer if it helps to solve the issue Visakh ----------------------------

    Sunday, March 02, 2014 6:27 AM