Here are the steps that will help you.
For the look up you have to create a dataset as in below screenshot. (While create it will ask for SQL server linked service and the table name(optional))
Then in the next step you can select the proc that you are trying to call as in screenshot 2.
Debug will give you this result. the result key can be different in your case. That is the column header from your proc.
If your proc is returning values in integer, please create a variable with string as we don't have int support type for variable.
We will convert the output of the stored proc into string using string() function. please use the below code in the set variable value field. I have used result at the end you have to use you column name
Your second question is why not stored procedure activity
Reason : Stored procedure activity does not capture the result dataset. So we won't be able to read the output. Procedure activity can be used to call the procedure without waiting for response.
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".