Execute SQL Server stored procedure through SSIS

Question

I have this stored procedure:

CREATE PROCEDURE [dbo].[sp_Carrier_Scan_Compliance]
     (@RETAILERID  INT OUTPUT,
      @SYSTEM_ID  VARCHAR(10) OUTPUT)
AS
BEGIN
    SET @RETAILERID = 2
    SET @SYSTEM_ID = 'DMASOS'
    ...
END

I have created a SSIS package using a Execute SQL Task in the control flow.

These are my Execute SQL Task editor settings:

This are my Variable settings:

These are my Parameter Mapping settings:

When I run the SSIS package, I get an error:

Error: 0xC002F210 at Execute SQL Stored Procedure (to copy data from 'BI-Datatrunk' source table) Task, Execute SQL Task: Executing the query "exec = [sp_Carrier_Scan_Compliance] ? OUTPUT, ? O..." failed with the following error: "Incorrect syntax near '='.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Stored Procedure (to copy data from 'BI-Datatrunk' source table) Task
Warning: 0x80019002 at Carrier_Scan_Compliance_SP: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

I am not sure what I am missing.

Please help me.

Thanks

You have to set "ResultSet" fro m"None" to "full record" (or something like this... otherwise Exec SQL Task has no result set... — Tyron78
@Tyron78 If I add that, I am getting below error Error: 0xC00291E2 at Execute SQL Stored Procedure (to copy data from 'BI-Datatrunk' source table) Task, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset". — user2331670
Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
I just created the procedure in a Test DB and modifies the exec sql as follows: change back to "Resultset none", change SQL statement to EXEC [sp_Carrier_Scan_Compliance] ? OUT, ? OUT. The remaining settings I left untouched / as you described - worked like a charm... — Tyron78

Mazhar Mazhar · Accepted Answer · 2017-11-02T14:25:21

The key part of the last error is

The EXECUTE permission was denied on the object 'sp_Carrier_Scan_Compliance', database 'DATAK', schema 'dbo'."

You need to assign EXECUTE permissions to the SQL user executing the Proc

USE DATAK
GO
GRANT EXECUTE ON sp_Carrier_Scan_Compliance TO <sql user>
GO

Execute SQL Server stored procedure through SSIS

1 Answers