SSIS derive column to parse number part

Hi, expert,

I need help with syntax for a string parsing  task in SSIS.  I have a SSIS package. the input column is a string which may contain number. those number could be either year (4 digits) or xth week (1 or 2 digit). Is there a way to do it inside derive column transformation? In other words, I would like to have the derive column perform something like below.  Thanks in advance

input column                           derive column
"happen in year 2000'            2000

or
input column                            derive column
"happen in 23th week"            23

or
input column                            derive column
"happen in  5th week"            5


rmtogetherAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Reza RadConsultant, TrainerCommented:
not with derived column,
use script component transformation instead,

add a script component transformation , let's assume A is input column , and B is output column ( datatype : int).
set language as Visual C#,
in script window , write code below in ProcessInputRow method:


public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        System.Text.RegularExpressions.Regex reg = new System.Text.RegularExpressions.Regex(@"\D");
        Row.B=Convert.ToInt32(reg.Replace(Row.A, ""));
    }

Open in new window

rmtogetherAuthor Commented:
thanks, I am using C# as well and I think this is something I am looking for.

but I have an run-time error in your code. it says the input string was not a correct format.
my input string is using DT_WSTR and my input_sting looks like the attached figure

is this because of my not all rows has numbers?
 so if don't have number. I would like to leave it as empty output.

please help me about this. I really need it asap
Snap4.jpg
Reza RadConsultant, TrainerCommented:
yes, this is exactly because of values without any digit inside,
I changed code, try this one,
As I supposed B output column of datatype INT, so I put 0 value for input values without any digit.

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
	
        System.Text.RegularExpressions.Regex reg = new System.Text.RegularExpressions.Regex(@"\D");
	if(reg.Replace(Row.A, "")==string.Empty)
{
Row.B=0;
}
else
{
        Row.B=Convert.ToInt32(reg.Replace(Row.A, ""));
}
    }

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rmtogetherAuthor Commented:
thank you for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.