Incorrect syntax near the keyword 'SET'.

There could be several issues why TX shows you this message
1. Does the query run in SSMS?
2. How do you do this insert? What I do is I create a custom view and use that to create an physical table (drag and drop). Make sure that you've read the view fields.
3. You should map all the tables and fields used by dragging them from the right pane to the left box. Make sure that the tables are Raw or valid (and if that is what you want).
4. I only see the dbo.retainexcel... it could be that the mapping below requires you to delete the dbo.
5. It might me a good idea to also specify from which database it should take the data. for instance fromt he DSA database and then (without the dbo.) is should look like: DSA.RetainExcel.... 

Hope this helps

Hi M

To add to what Daniel added, I have an guide not about custom table inserts, but I actually use them to do what I want to happen.
Improve execution times by splitting exchange rates out on dates

How I apply one of these splits requires an stored procedure and an custom table insert.

First the stored procedure.

Notice that I have mapped all fields that are existing, this is always best practice to do so.

Now to use the stored procedure i have an custom table with an custom table insert.

So I do not use the WITH CTE I just start. How these are applied are not always the same as in management studio.
If it just is an simple union of two tables, remove that first part and just have the select query.