These clean up jobs are always tricky.

I think you're doing the right thing already, but I'd go even further with it. The first part is trying to standardise the input as much as you can so you can set up rules downstream. e.g. In a derived column, use an expression to replace your '+' with 00, remove any brackets and spaces and perhaps replace any single leading zeros with 00 if the length of the string is greater than say 10. Now you should have just a string with a list of numbers. Hopefully you have trapped all the possible variations and standardised them in this step.

I would then split the rows (conditional split) using this derived column into international numbers (00 prefix) and national numbers. Further work is going to be required on the international ones. What you could do is create a reference table for your international dialling codes and use a look up to return the codes to the pipeline based on say the first 3 characters of your string after the leading 00. If a record is found, then use an expression to cut those left most characters out of your string.

Following that if you have any remaining, you should be able to identify a particular rule that applies to these so you can include them in the cleanup too.

Finally when you insert these into the destination, I'd want to keep the countryid as a reference even if its not part of the telephone number column as you never know when you might want to use it.

Hope this helps.