Can you use a stored procedure as the source for Lookup Transaction because it seems to fail on the EXEC statement?

  • Where exactly are you putting EXEC procedure_name? Try under Modify the SQL statement on the Advanced tab. If that's where you are doing it, then the error message is probably telling you the truth - it needs to be a table, a view, or a SQL statement (e.g. a SELECT query, not an EXEC). – Aaron Bertrand Feb 7 '17 at 20:42
  • @AaronBertrand. EXEC procedure_name is the the query. Found that if I replace that with SELECT * FROM OPENQUERY() in the Connection Tab that it works. Don't like having to use OPENQUERY. – ArgeeSix Feb 7 '17 at 20:49
  • Yeah that is an ugly workaround. – Aaron Bertrand Feb 7 '17 at 20:51
  • @AaronBertrand But necessary because the SSIS package uses the same lookup 32 times and I would hate it if I had to ever change the logic. – ArgeeSix Feb 7 '17 at 20:59
  • 1
    @AaronBertrand Thanks for showing me my blinders. It was vendor provided code. The stored procedure could just as easily be a view. – ArgeeSix Feb 7 '17 at 21:09

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;');
  • The other way around this is to switch from no cache to full cache. Full cache will allow you to use a stored procedure. – ArgeeSix Feb 7 '17 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.