none
Removing unwanted special characters from a column

    Question

  • All,

    I have a scipt that removes special characters and duplicate spaces from a column in dts 2000, but i have no idea where to implement this into my SSIS package. Is this a script task during data conversion or a derived column, etc.

     

    The column in question is an open comments field in the application so i need to remove all of the above stated characters and spaces. Here is a copy of the old active x script:

     

    '**********************************************************************
    '  Visual Basic Transformation Script
    '************************************************************************

    '  Copy each source column to the destination column
    Function Main()

     ' Removes unwanted tabs, carriage returns, line feeds, and excessive spaces

     DTSDestination("Comments") = Trim(DTSSource("Comments"))

     If Len (DTSSource("Comments")) > 0 Then

      ' Remove tab, line feed, carriage return
      DTSDestination("Comments") = Replace(Replace(Replace(Replace(DTSSource("Comments") , chr(13),""),chr(10),""),chr(9),""),","," ")

      ' Loop through the comment field and remove all instances of spaces > 2, then set it to equal to 1 space
      Do While InStr(1,DTSDestination("Comments"), String(2, " ")) > 0       
       DTSDestination("Comments") = Replace(DTSDestination("Comments"), String(2, " "), " ")
      Loop 

     End If

     ' Checks if the length of the field is greater than 2900. If so appends a message to the field.
     If Len(DTSDestination("Comments")) > 2900 Then
      DTSDestination("Comments") = Left( DTSDestination("Comments") , 2900 ) & " TOO LONG!"
     End if

     Main = DTSTransformStat_OK

    End Function

     

    Thank you all in advance.

    Sincerely,

    Brian

    Thursday, June 26, 2008 7:32 PM

Answers

  • The place to implement this type of custom logic is in the Script Transformation in the data flow. Because SSIS doesn't use VBScript, you'll need to rewrite the code in VB.NET (or C# if you're using SQL 2008) but that doesn't look too difficult.

     

    Thursday, June 26, 2008 7:46 PM

All replies

  • The place to implement this type of custom logic is in the Script Transformation in the data flow. Because SSIS doesn't use VBScript, you'll need to rewrite the code in VB.NET (or C# if you're using SQL 2008) but that doesn't look too difficult.

     

    Thursday, June 26, 2008 7:46 PM
  • Brian,

     

    You can try in Derived Column Transformation by just adding Replace() in the expression column.

    This should work.

    If you want to add the whole script as it is then you should use Script transformation.

     

    Friday, June 27, 2008 12:24 PM
  •  VBJ wrote:

    Brian,

     

    You can try in Derived Column Transformation by just adding Replace() in the expression column.

    This should work.

    If you want to add the whole script as it is then you should use Script transformation.

     

     

    Because the original solution requires looping over the input column an arbitrary number of times to remove all occurrences of multiple spaces (for example, three spaces together would require two passes over the data to replace it with a single space) the Derived Column transformation will not do the trick, as there are no control-of-flow constructs in the SSIS expression syntax.

    Friday, June 27, 2008 12:45 PM
  • Ok, thank you all.

     

    I am working on this now and will let you know how it goes.

    Tuesday, July 01, 2008 6:47 PM