If you have Jeff Moden's infamous dbo.DelimitedSplit8K splitter function installed (link), this becomes fairly straightforward:

DROP TABLE IF EXISTS #SomeText;

CREATE TABLE #SomeText
(
    Name VARCHAR(500)...

Does your table have any keys or indexes?

What does 'not working correctly' mean? No results? Error message? Wrong results? Something else?

Note that if TRANSACTION_DATE is a DATETIME (rather than a DATE), BETWEEN is not the recommended way...

I also suggest you start using <> rather than != for 'not equal to'. <> is a more widely accepted SQL standard than !=, as far as I know.

Change your SELECT to

SELECT TOP 100 PERCENT

with the rest of the query remaining as it is.

If you switch the level back to 120, this stops happening?

'First' = first ever, or first since instance was started, or first time in current day?

I have never used Jetbrains rider, so no. But as database properties are created in VS before the creation of any DACPACs, I suspect that it cannot be done.

Have you added the variable to the SQLCMD Variables collection in Database Properties?

I do not know the answer to your question, but I do have a suggestion. Rather than calling a stored proc to do the column-name checks, keep things inside SSIS...

Is this just a one-off, or are you designing a process which needs to run regularly and automatically?

I am as puzzled as you.

Have you tried playing around with the number of rows of comments to see how (or whether) that affects anything?

You could also try changing the...

This is what I've come up with so far.  Can someone grade me on this, or say if you think there's a better way?

with ad as...

This is a requirement, not a...

Use a query to get your data from SQL Server into SSIS and use CAST in the query to get the BLOB into string format

DROP TABLE IF...

Please provide your sample data in the form of CREATE TABLE/INSERT statements.  Also provide your desired results,  based on the sample data.