The datetime data type has no format. When you convert it to a string type, then yes, you can apply a format that matches the language, but inside datetime is an agnostic of the locale.
I preface my answer with an ADO.NET source that is much more painful than OLE counterparts.
In my playback, I have an Execute SQL Task that creates a table and stores some data in it. He then proceeds to the task of data flow.
In my data stream, I start by querying SELECT T.* FROM dbo.[Table] AS T There is currently no WHERE clause. This is here to allow data flow components to record the metadata of the original request.
To parameterize it, you need to return to the control flow level. Select the data flow task and in the "Properties" section, you will need to add an expression for the SqlCommand property of the ADO NET source.
Experience has shown that it is best to develop expressions for a variable and assign a variable to the Task property compared to creating it in the Task itself. If for some other reason this approach allows you to set a breakpoint and look at local residents and visually check the value. This cannot be done to express an object, especially if the specified expression causes a packet failure.
To this end, you will see that I have defined two variables in my SSIS package.
- InputDateV - The data type is DateTime, and it has a value of
10/24/2012 12:01 AM . I added a time component just to display it, but you can disable it to suit your needs. - Query - The data type is String. These variables are EvaluateAsExpression = True, and the expression is
"SELECT T.* FROM dbo.[Table] T WHERE T.DateAdded > '" + (DT_WSTR, 24)@[User::InputDateV] + "'" Here I force the value agnostic datetime value in the locale, but due to the magic of .NET code it will work.
Then I use @ [User :: Query] to configure [ADO NET Source]. [SqlCommand] and, oddly enough, everything works.
Otherwise, I misunderstood your definition regarding your variable names. If @ [User :: InputDateV] is actually a String type, then the above will not work for you. I created the variable @ [User :: InputDateS] of the data type string and assigned it the value 24/10/2012 . If I modify the @ [User :: Query] expression to use it, SQL Server will reject it because it cannot make this value as datetime data. Thus, the SSIS expression performs the translation. Change the expression to @ [User :: Query] so that it is "SELECT T.* FROM dbo.[Table] T WHERE T.DateAdded > '" + (DT_WSTR, 24)((DT_DATE) @[User::InputDateS]) + "'" and Bob is your uncle, green boxes through the package.
Link