Error with execute SQL task when using output parameter-CodePudding

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.

CodePudding user response：

This is a data type issue.

Based on the

CodePudding user response：

you should do something like below:

DECLARE @ExString NVARCHAR(500) 
SET @ExString = N'SELECT MAX([MYDATE]) AS MaxOfDate FROM TBLLOG
WHERE COMPLETED = 1 '
EXECUTE(@ExString)