I don't think there is a pre-baked recipe for you, but FWIW, Brent Ozar has a few recommandations, which you can read about in this blog post:

https://blog.quest.com/demystifying-cxpacket-and-cxconsumer-wait-types-in-sql-server/

Sorry, but you've been extremely sloppy with your example code/data.

1. The insert into statement can't even pass a parse test.
2. There are different column names used in the table and...

... but if you can't change the date format in the file name...

This would also be a possible solution:

DECLARE @x VARCHAR(100) = '09142023';

SELECT @x as [@x]
 ...

Make the filename use the international ISO format YYYYMMDD instead of that quaint american date format and you'll have no trouble at all. 🙂

That's not the intent of the "quiet" switch.  The intent...

One comment: I hope the example table isn't an exact replica of your production table, because to use that many columns defined as nvarchar(max) just to be on the safe...

... and here's my attempt at fulfilling your requirements:

/* Load the test data */

DROP TABLE IF EXISTS #SecurityUsers;

CREATE TABLE #SecurityUsers (
    SecCode NVARCHAR(70) NOT...

• This reply was modified 2 years, 8 months ago by kaj. Reason: SQL formatting
• This reply was modified 2 years, 8 months ago by kaj.

Have you changed the target server of the SSIS package to SQL Server 2019, done a Rebuild and deployment? If the target server of the package is still set for...

This is not an aswer to your question, but...

Your criteria AND (ITPRDC != 'AX' OR ITPRDC != ' ') looks wrong to me. And it's not your use of the...

• This reply was modified 2 years, 8 months ago by kaj. Reason: added "on the same column" to avoid ambiguity

Yes, 'BL.2112.00627' isn't the same as 'BL.2111.00627', so not surprising that you may get different results from the two queries. 🙂

As the OP want an opinion about the two code options presented, i.e.

Option 1:

DECLARE @strcol1 varchar(max), @intco11 int

SELECT @strcol1 += ','+CAST(col1 AS VARCHAR(20)),  @intco11=col1 
FROM (
...

Or you could dispense with some paranteses, like this:

IF EXISTS (

SELECT DISTINCT UPC,  U_PUBLISHER_TYPE
FROM [snapshot]
LEFT OUTER JOIN [item] 
ON [UPC]=item.[ITEM]
WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP')

EXCEPT

SELECT DISTINCT...

Unless the "with the matching dates" is to be interpreted as a date range selection, in which case Jeff Moden's integer math would be needed to ensure that the 24...

• This reply was modified 2 years, 11 months ago by kaj. Reason: Forgot a couple of t. column prefixes

And now we're just waiting for Google's Bard chatbot to go to GA. Time will tell who is the more truthful chatterbox.

But Bard will probably not do any better, since...

It apparently cannot make the distinction between "temp tables" and "temporary tables".  It's seriously incorrect for what most of us refer to as "temp tables" and...