SSIS Stored Procedure Call

Question

I'm trying to call a simple stored procedure which would return a list of names in normal test format, all in a single line. I'm passing it two parameters, but no matter how i setup the call, either within a OLE DB Source Editor, or within an execute SQL task. There must be something i'm missing with my SQL statement b/c i keep getting an error.

My SQL command text is

EXEC [dbo].[spGetEmployerIdCSV]  ?,  ?

The parameters I'm passing are listed exactly as they are declared in the stored procedure, @IDType and @IDNumber, which are mapped to predefined variables.

Every time I try to run it from either task type, I get a

The EXEC SQL construct or statement is not supported.

What is the best way to run a stored procedure within SSIS?

Thank you.

Answer 1

I cannot recreate your issue.

I created a control flow with the proc already in existence.

I have my execute sql task configured as

My parameters tab shows

When I click run, the package goes green.

My initial assumption was that you had signaled that you were using a stored procedure and were erroneously providing the EXEC part. I had done something similar with SSRS but even updating the IsQueryStoredProcedure to True, via Expression, I could not regenerate your error message.

If you are doing something else/different/in addition to what I have shown in the Execute SQL Task, could you amend your question to describe what all functionality the procedure should show.