1

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

6
  • 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). Feb 7, 2017 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, 2017 at 20:49
  • Yeah that is an ugly workaround. Feb 7, 2017 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, 2017 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, 2017 at 21:09

1 Answer 1

3

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;');
1
  • 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, 2017 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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