Stored procedure in SSIS Lookup Transformation Editor
I would suggest trying to put the stored procedure logic into a view or table-valued function, which could then be referenced in the lookup.

If that can't be done, then you can use the ugly workaround you already discovered:

SELECT * FROM OPENQUERY(LoopBackServer, N'EXEC db.dbo.procedurename;');