Hi All

i am having this issue where calling a Stored Procedure from Execute SQL task runs fine for First time and next call throws error.

SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Cannot use the OUTPUT option when passing a constant to a stored procedure.". 

Unable to retrieve destination column descriptions from the parameters of the SQL command. 

component "Call Insert Procedure 1" (499) failed the pre-execute phase and returned error code 0xC0202080.  

See the snapshot from SSIS how stored Proc is called.

Here is the Stored Procedure

ALTER PROCEDURE [ETL].[usp_insertRelationshipVerb]

      @RelationshipVerb varchar(255)

      ,@Gender nvarchar(255)

      ,@RelationShipKey  INT OUTPUT

AS

      BEGIN TRAN

            DECLARE @RSKey int

            IF NOT EXISTS(SELECT 1 FROM [dbo].[Relationship] WHERE RelationshipVerb = LTRIM(RTRIM(@RelationshipVerb)) AND Gender = @Gender)

            BEGIN

                  INSERT INTO [dbo].[Relationship]

                  (RelationshipVerb,Gender,RelationShipGroup)

                  Values (@RelationshipVerb,@Gender,'other')

                  SET @RSKey = SCOPE_IDENTITY()

                  SET @RelationShipKey = @RSKey

            END

            ELSE

            BEGIN

                  SELECT @RSKey = RelationshipKey from [dbo].[Relationship]

                        WHERE RelationshipVerb = LTRIM(RTRIM(@RelationshipVerb)) AND Gender = @Gender

                  SET @RelationShipKey = @RSKey

            END

      COMMIT TRAN

This was working for last 3 years and since last one month we are having this issue intermittently.

We run the SQL agent job and in that SSIS package is called.

Any advise is appreciated.

Hi Neeraj,

Please find the reason behind the error in below link

http://sqlserverbuddy.blogspot.in/2011/08/cannot-use-output-option-when-passing.html

Versions:
All versions of SQL Server.

Example(s):
CREATE PROCEDURE dbo.uspT (@p1 int OUTPUT)
AS
SELECT @p1
GO
DECLARE @p1 int;

EXEC dbo.uspT 1 OUTPUT;
DROP PROCEDURE dbo.uspT;

use following

http://www.sql-server-performance.com/2009/cannot-use-output-option-when-passing-a-constant/

Hi 

Where do you see a constant passed to Stored Procedure ?

i am inserting a row in table which has identity column and i am passign that new identity value to Output parameter