Execute SQL Server stored procedure through SSIS

debugcn Published at Dev

user2331670

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

Mazhar

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-08-7

Comments

0 comments

From Dev

SQL Server throws timeout when I execute a stored procedure through an application, but not when I execute it in the Management Studio

From Dev

Not seeing "Execute stored Procedure" in SQL Server 2016

From Dev

Create and execute stored procedure in SQL Server

From Dev

Create and execute stored procedure with parameter in SQL Server

From Dev

SSIS Execute SQL Task Stored Procedure returning empty result sets

From Dev

Execute SQL Server stored procedure through VBA which does not return rows

From Dev

tsql not firing (stored procedure via ssis) - SQL Server 2012

From Dev

Execute stored procedure for each row in the table in SSIS

From Dev

SQL Server: EXECUTE AS clause of stored procedure not granting sysadmin permissions

From Dev

Execute a SQL Server stored procedure from MS Access

From Dev

How to find all users with execute rights on a stored procedure in SQL Server

From Dev

How to Execute a Stored Procedure in a Join Statement SQL Server

From Dev

Execute stored procedure with a nullable parameter from SQL server management studio

From Dev

Execute multiple statements and CTE in stored procedure in SQL Server 2012?

From Dev

Does grant ALTER imply grant EXECUTE to a SQL Server stored procedure

From Dev

Stored procedure taking long time to execute in SQL Server 2008

From Dev

SQL Server: EXECUTE AS clause of stored procedure not granting sysadmin permissions

From Dev

Execute stored procedure with a nullable parameter from SQL server management studio

From Dev

Execute Stored Procedure with RollbackTrans using VBA and SQL Server 2012

From Dev

Power BI. Execute SQL Server stored procedure with a user parameter

From Dev

SQL server : stored procedure

From Dev

Stored procedure with optional parameter that doesn't show in SQL Server Management Studio "execute stored procedure" or "script stored procedure"

From Dev

Execute stored procedure in mvc through EnterpriseLibrary

From Dev

Unable to execute Stored Procedure through SSMS and VS

From Dev

How to execute two Oracle stored procedure from a SQL Server 2008 R2 stored procedure using linked server

From Dev

SQL Server Stored Procedure Parameter

From Dev

sql server stored procedure IN parameter

From Dev

Keyword SQL Server stored procedure

From Dev

SQL Server: using IF with as in stored procedure

Related Related

Article