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.
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.
- Can anyone please explain this solution of hacker rank Binary Tree Nodes?
- Nesting queries in SQL
- Extract previous row calculated value for use in current row calculations - sql
- How can I select rows from a table where the sum of rows in a joined table is greater than a number?
- Generating time series between two dates in PostgreSQL
- Dates with no time or timezone component in Java/MySQL
- Group close happening events together in ClickHouse
- Is it possible to create a temp table on a linked server?
- Get most recent using datetime
- How can I delete one of two perfectly identical rows?
- How to use SELECT DISTINCT and CONCAT in the same SQL statement
- Mysql query search a string in all columns of a table
- Are PostgreSQL column names case-sensitive?
- Django: how to effectively use select_related() with Paginator?
- PHP: exec() returns status '0' (successful) when executing failing PostgreSQL scripts
- SQL Azure import slow, hangs, but local import takes 3 minutes
- Is TRUNCATE a DML statement?
- MySQL Cannot Add Foreign Key Constraint
- Max and Rank not returning expected results
- Boolean vs tinyint(1) for boolean values in MySQL
- What is the syntax for an inner join in LINQ to SQL?
- "item has already Been added" added in audit
- Merging 2 tables from different databases using MERGE INTO
- How to query a dynamic list of strings using SQL?
- Gaps between primary key id in sql table
- DB Design: how to indicate if a column data is public or private
- SQL Server + convert to text to HEX UTF16 Little Endian
- TO_CHAR to format decimal places not working for whole numbers
- Extracting value from comma-delimited string in Teradata
- How can I shuffle rows in a SQL Server table after insert? Newid doesn't work as expected for some reasons