T-SQL Convert UTC time from cloud server to local time with daylight savings

Question

I have an Azure cloud SQL DB located in UTC timezone, but I'm located in Central Europe with daylight savings half of the year. In an ETL process I use GETDATE() to fill a column to tell me the last datetime my data was refreshed. Because the machine this is done at is in UTC, the result from GETDATE differs from the time in my physical location.

I've searched for solutions but the two most suggested solutions do not seem to work. Firstly to use GETUTCDATE and GETDATE to determine the difference and use this will not work because both functions return the same datetime. The function AT TIME ZONE will not work because half the year my timezone is Central European Time (CET), the other half in Central European Summer Time (CEST). The date the zones switch is different each year so I cannot use a check to see if the date is between two dates.

I feel there should be some solution for this I haven't thought of yet, as I cannot be the first to encounter this. Curious if someone has a fix. Thanks in advance!

Found two solutions as described above, but logically they would not work due to the cirumstances. Haven't found a solution which solves this in a fairly easy manner.