Agreed. I'm not at all an SSIS guy beyond spelling it, but if you're going to do this in T-SQL, an alternate approach would be to stop thinking of it in terms of parsing the string by character and start thinking of splitting it out by delimiter. If you're not familiar with Jeff Moden's DelimitedSplit8K function, the article is at http://www.sqlservercentral.com/articles/Tally+Table/72993/. It'll change the way you look at data and think about performance.

with cte(filename) as (

select 'MyCustomerName_Invoice_12345678.pdf' union all

select 'YourCustomerName_Invoice_23456.pdf' union all

select 'SomeLongerCustomerName_Invoice_123.pdf' union all

select 'ShortName_Invoice_7891234567890.pdf')

select filename, s.item

from cte

cross apply DelimitedSplit8K(cte.filename, '_') s

where s.ItemNumber = 3;