Search code examples
sqlsql-serverssisetlexecute-sql-task

Error with execute SQL task when using output parameter

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.

Solution

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

    enter image description here