Search code examples
ssisssis-2008

SSIS match datetime variable to varchar in data flow

I have a table that have a column storing date in varchar(10) format, like '20190101'. I have a variable in SSIS is defined as DateTime format. now I want to make the value match in SSIS OLE DB data flow source, in the format as SELECT * FROM table where date = CONVERT(varchar(10), ?, 112).

Looks like the conversion didn't work correctly; no qualified data pops out in the data flow.

So what's wrong with this query (the result looks fine when putting it into SSMS), and how to debug it (it's not possible to debug it using the query like SELECT CAST(? AS varchar(10) FROM table in data source).

(PS: the last thing I want to do is to define another string variable to work around).

Solution

  • The reason is because of implicit conversion by SSIS data source when deciding the type of parameter according to the opposite side of operator. Try this test:

    1. Make a valid SSIS data source connection to a table that contains a date(time) column; make a dateTime variable.
    2. Switch the data source to SQL command and type the below queries, one by one:

    SELECT 1 FROM table WHERE ? > 1

    SELECT 1 FROM table WHERE ? > '1'

    SELECT 1 FROM table WHERE ? > date_col

    1. Execute the data flow.
    2. In SSMS, quote the recent sessions:

    select top 50 t.text, s.* from sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text(sql_handle) t where t.text LIKE <filter your session> order by last_execution_time desc

    You may find how the parameter is interpreted:

    (@P1 int)SELECT 1 FROM table WHERE ? > 1

    (@P1 varchar(8))SELECT 1 FROM table WHERE ? > '1'

    (@P1 datetime)SELECT 1 FROM table WHERE ? > date_col

    In other words, the SQL command will interpret the type of incoming parameter regardless of what type it original was.

    So in my problem, the dateTime parameter was first implicitly converted into varchar with an unknown format than we are attempting to convert it into a specified date type:

    SELECT * FROM table where date = CONVERT(varchar(10), 'Jan 01 2019 12:00:00', 112)