I don't see much that could improve performance other than what @Malachi said. I did notice that (at least to my preference, completely subjective) your code would read easier with more vertical white space, especially in your join conditions and subqueries.

Here is how I personally would format it:

with cteSizedInvoices (
    InvoiceNumber, InvoiceLine, SizeRangeCode, UnitsPerSize
)
as (select
        src.f2,
        cast(src.f5 as int),
        src.f23,
        src.f19
    from Staging.dbo.[SourceTable] src
    where src.f1 = '01'
)
--insert into [DestinationDatabase].dbo.InvoiceDetailSizes (InvoiceDetailId, SizeId, Units, DateInserted)
select
    detail.Id InvoiceDetailId,
    sz.Id SizeId,
    buckets.Units,
    getdate()
from
    cteSizedInvoices src
        inner join [DestinationDatabase].dbo.InvoiceHeaders header 
            on src.InvoiceNumber = header.Number
        inner join [DestinationDatabase].dbo.InvoiceDetails detail 
            on src.InvoiceLine = detail.LineNumber
            and detail.InvoiceHeaderId = header.Id
        inner join [DestinationDatabase].dbo.SizeRanges ranges 
            on src.SizeRangeCode = ranges.Code
        cross apply (
            select id SizeIndex, 
            cast(item as int) Units 
            from [DestinationDatabase].dbo.BucketString(src.UnitsPerSize, 5, 1, 1)
        ) buckets
        inner join [DestinationDatabase].dbo.Sizes sz 
            on buckets.SizeIndex = sz.SizeRangeIndex
            and sz.SizeRangeId = ranges.Id
        left join [DestinationDatabase].dbo.InvoiceDetailSizes dst
            on detail.Id = dst.InvoiceDetailId
            and sz.Id = dst.SizeId
where 
    buckets.Units <> 0
    and dst.id is null;

I have found a few things like this post on SO and this post on DBA which seem to be about the nasty Nested Loop that happens when you join in your table valued function. The post on SO suggest you could force SQL to do a Hash Match like this for example:

        inner hash join (
            select id SizeIndex, 
            cast(item as int) Units 
            from [DestinationDatabase].dbo.BucketString(src.UnitsPerSize, 5, 1, 1)
        ) buckets

Hope this helps!