SSIS Strip digits from a string.

Today's scenario is a "File Name" that contains digits and need to strip these digits out, it can be at the beginning of the string, middle or at the end.

If you are wondering, why there's digits! it's the size of that file and only comes at the end of the string, but just to demonstrate various scenarios that you might encounter.

For our demo, I'll create a table, has 2 columns, an ID and a FilePath column.
the output will be 4 columns, our 2 input columns along with 2 new columns, FileSize and CleanedFilePath. Of course you can overwrite your FilePath with the digits stripped one.

Let's run the below script to create the test demo and insert 3 test rows.
<><>

SSIS Strip Digits

    /* Create Table */ 

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STripDigits]') AND type in (N'U'))
    BEGIN 

    CREATE TABLE [dbo].[STripDigits](
     [ID] [int] NULL,
     [FilePath] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    END
    GO


    /* Insert Test Records */ 

    INSERT INTO STripDigits VALUES
    (1,'6590FolderProcessed')
    ,(2,'Patients32PCP')
    ,(3,'Manual1817')


    /* Verify Records */ 

    Select * from STripDigits

Steps:

1-Basically I'll add a "OLE DB Source" to read from my SQL table, I'll assume you know how to configure that.

2-Will add a "Script Component", will use "Visual Basic .NET" as my scripting language.

Will grab the Input Column (FilePath)
Will Create 2 new output columns, a string for the cleaned File Path, and a File Size for the digits portion of the string.

Add that VB Script...
I'm using Regular Expressions here, so simple 2 lines of code..

Private StripDigits As New System.Text.RegularExpressions.Regex("\d")

    Private StripTxt As New System.Text.RegularExpressions.Regex("\D")




 Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        '

        Row.CleanedFilePath = StripDigits.Replace(Row.FilePath, "")

        Row.FileSize = CInt(StripTxt.Replace(Row.FilePath, ""))

    End Sub