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.

Answer 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.

Answer 2

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;