2

I'm taking the last updated date from SQL table as below:

SELECT CONVERT(DATETIME, [Value]) AS LastModifiedDate 
FROM [WarehouseDB].[dbo].[tblWarehouseSettings]
WHERE Name = 'LastModifiedDate'

[Value] is varchar.

A variable as below:

Variable

I'm using an Execute SQL Task to get the date value & assign it to the variable. My Execute SQL Task Editor values set as below:

General

Parameter Mapping

enter image description here

The task executed successfully but it doesn't get the value from the DB. Assigned value to the variable after the task execution is {12/30/1899 12:00:00 AM}

Can anyone figure out what I'm doing wrong here?

Hadi
  • 36,233
  • 13
  • 65
  • 124
kapz
  • 437
  • 1
  • 7
  • 15
  • Because the SSIS value is changing from `GETDATE()` to something else ...`12/30/1899 12:00:00 AM` then I think that SSIS is working. The issue may be in the LastModifiedDate column. Is it possible there are empty strings or NULLS resulting from that `CONVERT()`? Would you mind extending the WHERE clause to include dates only? Could you add a `AND ISDATE(CONVERT(DATETIME, [Value])) = 1`. This will filter the result set to dates only. Also, what is your datasource, SQL Server, Oracle? Thanks. – Troy Witthoeft Jul 31 '17 at 12:24
  • Can we see the full SQL Statement please ? – SQLAndOtherStuffGuy Jul 31 '17 at 12:28
  • Quick observation. You have specified that User:LastUpdatedOn is an Output parameter. If you are passing this directly to a parameterised query and assigning the result back to a variable then this should be set to Input. So, If my SQL is `SELECT Column FROM Table Where ID=?` then parameter 1 is to replace question mark and is set as an Input. – SQLAndOtherStuffGuy Jul 31 '17 at 12:33
  • 2
    Ok, thanks for the update. No need for the Parameter mapping then. It's redundant as you have no parameters in your query. The result set returns a single row with a single column that should be assigned to the LastUpdatedOn variable. This part looks like it's configured correctly. Delete the parameter mapping and try again. – SQLAndOtherStuffGuy Jul 31 '17 at 12:41
  • Also, I've noticed that in the properties of the Execute SQL Task you are using DATETIMEOFFSET, I'm pretty sure this needs to be assigned to as string variable on the way out. – SQLAndOtherStuffGuy Jul 31 '17 at 12:57

1 Answers1

1

There are 2 things that caused this issue :

  1. There is no need to specify @[User::LastUpdateOn] as an Output parameter in Parameters mapping Tab
  2. Your SQL Statement is showing that you are converting [Value] column to DATETIMEOFFSET instead of DateTime and DT_DBTIMESTAMP is related to DateTime SQL Data Type

References

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • DATETIMEOFFSET was there because I tried many variations and I've taken a wrong screenshot. Removing the added Output parameter in Parameters mapping Tab solved the problem! – kapz Aug 01 '17 at 03:42
  • @Hadi, just out of interest, can this same thing be achieve only with output parameter rather than making use of the result set? I tried doing this but get an error for datetime type: https://stackoverflow.com/questions/70504271/error-with-execute-sql-task-when-using-output-parameter – variable Dec 28 '21 at 08:38