Your syntax is nearly right, but not quite. You will have to explicitly name all of the columns, except for the IDENTITY column, which will take care of itself as...

Is the Id column an IDENTITY column?

That is a statement, not a question. It's similar to me saying: "I am not a turnip".

we tried to upgrade to 2019, but unfortunately, MS has put some restrictions like they have converted scalar functions to inline function by default and that throws an error...

As usual, code not formatted and no question is asked.

BUT even if you DO get it on your screen, there are a LOT of typos in it which makes it impossible to copy-paste into SSMS UNLESS you want...

Ah yes, that's much easier to read now, on my brand new 20-metre-wide screen.

Such a shame you're on 2012. It's time to upgrade! In 2017+, this solution works:

WITH r1
AS (SELECT R
         ...

Have a look at this link.

In order to use CommandTimeout, it seems you will first need to create an OdbcCommand and push your query through that.

I answered this question yesterday. Why are you asking it again?

Use single quotes for literals, not double quotes.

Date literals should be in the form 'YYYYMMDD'.

As usual, you have provided a wedge of code and not asked a question. Please try not to make people play 'Guess the question' when you are posting.

I've done some playing around with this and encountered exactly the same problem as you with the standard Excel source – a Jet error.

The way I got it to work...

Do you use any other software (outside of SQL Agent) to run scheduled tasks?

It's beginning to look a lot like ... a catch-all query.

I suggest you get yourself a nice drink and read this article: https://sqlinthewild.co.za/index.php/2018/03/13/revisiting-catch-all-queries/