Bind output parameter in a stored procedure to a variable in SSIS

  Kiến thức lập trình

I have the following stored procedure

CREATE PROCEDURE [log].[SP_LogExecutionStep] 
    @ExecutionGUID [uniqueidentifier],
    @ProjectName NVARCHAR(50) = NULL ,
    @PackageName NVARCHAR(255) = NULL ,
    @PackageVersion NVARCHAR(10) = NULL,
    @ParentPackageName NVARCHAR(255) = NULL,
    @ParentExecutionID INT = NULL,
    @ExecutionStatus NVARCHAR(50) = NULL,
    @ExecutionResult NVARCHAR(50) = NULL,
    @ExecutionMessage NVARCHAR(255) = NULL, 
    @ExecutionID INT OUTPUT
AS


BEGIN

-- Start Logging.
INSERT INTO log.T_LOG_Execution (ID_ExecutionGUID, LB_ProjectName, LB_PackageName, LB_PackageVersion, LB_ParentPackageName, ID_ParentExecutionId, LB_ExecutionStatus, LB_ExecutionResult, LB_ExecutionMessage, DT_ExecutionStartDate)
SELECT @ExecutionGUID, @ProjectName, @PackageName, @PackageVersion, @ParentPackageName, @ParentExecutionId, @ExecutionStatus, CASE @ExecutionStatus WHEN 'Running' THEN 'In Progress' ELSE @ExecutionResult END, @ExecutionMessage, GETDATE()

-- Set Output Parameter as the last Identity created to be used for subsequent logging.
SELECT @ExecutionID = CAST(SCOPE_IDENTITY() AS INT)

END

I am trying to bind the “ExecutionID” output parameter to an SSIS variable.

I seem to be missing something in my logic as I am getting the following error:
“Value does not fall within the expected range”.

When I tried removing the output parameter route and taking a the result as a result set it works fine.

I appreciate the help

I tried doing it without a output parameter and it works.

My aim is to use the output parameter approach.

LEAVE A COMMENT