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.

Solution 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.

Solution 2

Did you specify output parameters? For 2 in / 1 out your SQL code will look like:

EXEC [dbo].[spGetEmployerIdCSV] ?, ?, ? OUTPUT

ResultSet has to be set to none!

Solution 3

I had the same problem.

When you execute the task check the 'Progress' tab; this will give you a 'fully fledged' error details.

In my case I didn't map the parameter which I created in the SQL Task to the actual one in the Stored Procedure.

So, double click the SQL Task, click on Parameter Mapping on the left hand side, then Create the parameters and their respective mappings. Here is a screenshot (in version 2012):