Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.
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;');
Required, but never shown