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.

Answer 1

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.

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