Stored procedure in SSIS Lookup Transformation Editor Can you use a stored procedure as the source for Lookup Transaction because it seems to fail on the EXEC statement?
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;');