How to use datetime value in an SSIS expression builder to formulate SQL command?

Question

I am trying to use Ado.Net Sql command to select data from a table with a date filter.

SELECT  COLUMN1
    ,   COLUMN2 
FROM    TABLENAME 
WHERE   DATE_INSERTED > @[User::LastInsertDate]

Both Date_Inserted and @[User::LastInsertedDate] are of type DateTime. When I try to evaluate expression in the expression builder l get the following error;

The expression might contain an invalid token, an incomplete token, or an invalid elemnt, it might not be well-formed, or might be missing part of a required element such as a parenthesis.

Problem diagnosis from original revision of accepted answer:

Here is my understanding of your question. I believe that you created two variables under package scope. A variable named LastInsertDate of DateTime data type and another variable named SqlQuery of String data type to store the SQL SELECT command.

You set the EvaluateAsExpression property on variable SqlQuery to True. You then entered the following command SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE DATE_INSERTED > @[User::LastInsertDate]

When you clicked EvaluateAsExpression, you got the following error message:

Expression cannot be evaluated. Additional information: Attempt to
parse the expression "SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE
DATE_INSERTED > @[User:LastInsertDate]" failed. The expression might
contain an invalid token, an incomplete token, or an invalid element.
It might not be well-formed, or might be missing part of a required
element such as a parenthesis. 

Shanonshanta

Answer 1

The issue here is that the value you are trying to store in the variable SqlQuery is not enclosed within double quotes. The string value of the dynamic query should be enclosed within double quotes.

While enclosing the text in double quotes, you cannot use the datetime variable LastInsertDate as it is. You need to convert the date time variable to string but if you simply convert datetime value to string, you might land into unexpected format. To be on the safe side, I would recommend using DATEPART function to translate the datetime value to string of format YYYY-MM-DD hh:mi:ss. Here is the complete expression that will do that.

"SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE DATE_INSERTED > '" +
(DT_STR, 4, 1252) DATEPART("yyyy", @[User::LastInsertDate]) 
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm", @[User::LastInsertDate]), 2)
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd", @[User::LastInsertDate]), 2) 
+ " " + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("hh", @[User::LastInsertDate]), 2) 
+ ":" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mi", @[User::LastInsertDate]), 2)
+ ":" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("ss", @[User::LastInsertDate]), 2) 

When you click Evaluate Expression, you will see the string with the date time value in the Evaluated value section.

Hope that helps.

Daysidayspring

The drawback of this approach is that you can't extract milliseconds out of SSIS DateTime variable. If you are filtering by a table column of datetime format, you always get some delta. E.g. variable @[User::LastInsertDate] stores timestamp '2023-08-25 14:31:11', in table you have rows with [DATE_INSERTED]: '2023-08-25 14:31:11.250', '2023-08-25 14:31:11.600'. These two rows will always come with delta and timestamp will not move forward. – Mycostatin

Answer 2

There is an option to store your variable @[User::LastInsertDate] in String format. On writing to @[User::LastInsertDate] you can ensure proper formating with CONVERT function, e.g.:

convert(varchar ,@dt ,25) as TS;

Then you can just concatenate the variable with your query:

"SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE DATE_INSERTED > '" + @[User::LastInsertDate] + "'"

Mycostatin