Help with recursing self-referencing table I have two tables:

Datasource ( A self referencing table)
------------------------------
Id, Type, ParentId, Name
1   A     NULL      Foo
2   B     1         Bar
3   C     1         FooBar

Variable
------------------------------------
Id DataSourceId Name
1  3            Foo
2  2            Bar
3  1            Foobar


My desired output is something like:

Output
---------------------------------------------
VariableId  DataSourceId
1           1
2           1
3           1


Such that for each variable, the datasource is the topmost parent in the hierarchy.

I'm struggling with the required recursion of the self referencing table.
Start with the recursive CTE to get the tree. Keep track of the parent too (faking the bottom level), to identify when it's null.

WITH Tree AS (
    SELECT Id AS VariableId, DataSourceId, DataSourceId AS Parent
    FROM Variable
    UNION ALL
    SELECT t.VariableId, t.DataSourceId, d.ParentId
    FROM Tree t
    JOIN DataSource d
    ON d.Id = t.Parent
)
--Now get the top levels
SELECT VariableId, DataSourceId
FROM Tree
WHERE Parent IS NULL;