Error with execute SQL task when using output parameter

Question

I want to retrieve the latest date from a SQL Server table.

In an "Execute SQL task" I have the following SQL Statement:

SELECT ? = MAX(MYDATE) --SQL data type of this column is datetime
FROM TBLLOG
WHERE COMPLETED = 1

Under the parameter mapping section I have added 1 output parameter:

Variable Name: User:var_testdt (note: this is of type datetime)
Direction: Output
Date Type: Date
Parameter Name: 0
Parameter Size: -1

The ResultSet property on the "Execute SQL" task is set to None.

I get this error when executing the package:

... failed with the following error:
Error HRESULT E_FAIL has been returned from a call to a COM component.
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Hadi · Accepted Answer · 2021-12-29 06:45:51Z

1

This is a data type issue.

Based on the SQL Server official documentation, DT_DBTIMESTAMP data type in SSIS is mapped to the datetime and smalldatetime data types in SQL Server. While, DT_DATE is not mapped to any data type.

To solve this problem, just change the output parameter's data type from DATE to DBTIMESTAMP. Besides, make sure that the User:var_testdt variable is also DateTime.

edited Dec 29, 2021 at 6:45

answered Dec 28, 2021 at 21:29

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeremy Caney · Accepted Answer · 2022-09-07 00:08:33Z

-1

I use this. Note: @RETORNOERRO is an output parameter.

DECLARE @SQLString nvarchar(max), @ParmDefinition nvarchar(2000)
SET @SQLString = 'IF NOT EXISTS (SELECT 1 FROM DBO.ITEM_PEDIDO ITE (NOLOCK) WHERE COD_PEDIDO = @COD_PED_AVS_RETORNO ) ' +
    'BEGIN ' +
        'SET @RETORNOERRO = ''Dado não inserido. Houve algum erro no insert dos itens, Verifique. Pedido_app: '' + @P02_CODIGO_PEDIDO 
    END';
SET @ParmDefinition = N'@COD_PED_AVS_RETORNO int, @P02_CODIGO_PEDIDO varchar(10), @RETORNOERRO varchar(200) OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition, @COD_PED_AVS_RETORNO, @P02_CODIGO_PEDIDO, @RETORNOERRO OUTPUT;

edited Sep 7, 2022 at 0:08

Jeremy Caney

7,818115 gold badges58 silver badges86 bronze badges

answered Sep 1, 2022 at 14:26

user7593326

1

Collectives™ on Stack Overflow

Error with execute SQL task when using output parameter

2 Answers 2

This is a data type issue.

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

This is a data type issue.

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related