Single quotes are for chars. Use double quotes for string literals ... it should work.

Great. How long does it take on your live table?

Thanks for posting the link. I did not know about the 1000-row limit.

Have you tried importing a file into a table containing a column of XML datatype and then querying that? I have no idea whether it will be faster, but might...

No need for UNION:

INSERT [PW].[Contract] ([ID],[ProgramID],[PAContractNum],[PAContractAmt],[PAContractStartDate],[PAContractExpirationDate],[CreatedBy],[CreatedOn],[UpdatedBy],[UpdatedOn]) VALUES (1, 8, '15FHBFPP0115', 0.0000, '2014-07-01 00:00:00', '2016-06-30 00:00:00', 'some.person', '2015-02-11 00:00:00', 'some.person', NULL)
 ,(2, 8, '15FHBFPP0102', 0.0000, '2014-07-01 00:00:00', '2020-06-30...

If your Script Task can access the .DTSX file corresponding to 'itself', it could read the variable names from it. Not pretty and not recommended, but not impossible either.

Your existing method is probably the most practical. I considered other options and came to the same conclusions as you. I suggest that you stay well away from the XML...

It always happens that way with such "no one made any changes" such things. 😀

It would be nice if you could post the actual code you're talking about so...

Please take a look at this and see what you think

DROP TABLE IF EXISTS #LastTimeTankDrained;

CREATE TABLE #LastTimeTankDrained
(
    Tank CHAR(2)
   ,LastDrain INT
 ...

What the data represents is confusing me a little, can you expand on it a little, please?

Take TankDate 1/1/2021 as an example. There are 6 rows, with DrainSeq ranging from...