That is it, thanks Scott!

Thanks for your input, Jonathan.

The single query method did not work, even after changing s2.Value to s2.ColName in the necessary spots.

I have gone ahead with the multiple-batch version (there are...

As you have not provided us with any sample DDL or data, we cannot give you a coded solution. But the answer to the question: "Can queries in SQL Server...

You can do this using REVERSE, or a splitter (link). A splitter provides a more flexible and elegant solution, in my opinion:

DROP TABLE IF EXISTS #sample;

CREATE...

Then add a switch to the code … this sort of thing:

IF (DEV)
    DEBUGMODE="Verbose" 
ELSE...

The least-disruptive way of doing this which I can think of is to add a script task at the end of your existing SSIS process which splits your output file...

Changing the .NET app so that it only loads the data which is required is not possible, I presume?

You'll probably get more help with this problem if you attach the actual execution plan.

Run this code:

DROP TABLE IF EXISTS #sample_node;

CREATE TABLE #sample_node
(
    id INT
   ,label VARCHAR(10)
   ,realdate DATETIME
   ,datedif INT
);

INSERT #sample_node
(
...

I always forget about updating the CTE directly. Here is a better version:

WITH calcs
AS (SELECT id
          ,label
 ...

Something like this?

WITH calcs
AS (SELECT id
          ,label
  ,realdate
         ...

If you have five columns in a table and the only PK which 'works' is a combination of all five columns, there can be no updates to the data.

Why not?...

This could also be done in Posh.

Have a go and see how you get on. Feel free to post back ... if you cannot get this working, I will put...

I know nothing about this specific problem.

But adding a Script Task to edit and save the file before onward processing should be straightforward. Can you write a little C#?

Why not try and...