Search code examples
ssispatindex

How to remove prefixed numbers from a varchar in SSIS?

I have an SSIS package which copy data from text file to a table in sql server.

I use 3 tasks for the same 1)Flatfile Source 2) Derived Column task, 3) SQL Destination task

In the 3rd task..I specify the table...into which I have to copy the data.

In that destination table..there is a column DESC..and it's of type varchar...and flat file contains data for this column as follows :- "01 planA".."04 plan C", "PlanJ".

I need to remove these prefixed numerics. I have a query as below..but I can use this in derived column task..as SSIS wont support PATINDEX.

SUBSTRING([DESC], PATINDEX('%[a-zA-Z]%',[DESC]), LEN([DESC])- PATINDEX('%[a-zA-Z]%',[DESC])+1)

Please help me.

Solution

  • You could do this in a Script task, using Regular Expressions. You'll need to reference System.Text.RegularExpressions then use the .NET Regex object to mimic the code above in the Input0_ProcessInputRow method.

    Alternatively, send the raw data firstly to a SQL staging table and extract from there using PATINDEX as in your example, then push the cleaned version to your destination table.