Post reply

SQLserver datetime and SSIS SCD issues

  • October 6, 2011 at 5:24 pm

    #244961

    I have a weird thing going on here. I've been searching for a long time with no real good answer.

    The SQL2008 database column is Datetime, which is YYYY-MM-DD hh:mi:ss

    The SSIS OLE DB Source detects it as a DT_DBTIMESTAMP which is YYYY-MM-DD hh:mi:ss.zzz (as seen in the Dataviewer)

    Now when the Slowly changing dimension performs a select statement, the returned value has a fractional component to it (as seen by the Dataviewer), which looks exactly the same as the incoming record (as seen by the dataviewer), but it still detects it as a change between what is coming in and what is selected, thus tries to update and insert a new record even if nothing else on the record has changed. I suspect that there still is a difference between the converted DBTIMESTAMP value and what SSIS is selecting from the table during the SCD component, but how do you work around this without hacking the column into a string?

    Seems there is no datatype which has YYYY-MM-DD hh:mi:ss as a pattern in SSIS, which imho would solve this easily.

    Now the easy fix was to convert everything to DT_DBDATE or DT_DATE in the Dataflow, and everything works, except you can’t UNION ALL the OLE DB Source error output with the rest of the Data Flow to redirect the column if a conversion error occurs, and the inserted value gets truncated to 00:00:00 hours, which isn't appropriate.

    Basically, I want to use a datetime column in my slowly changing dimension. This should be a no brainer, but I've tried almost every datatype and combination and I am still stuck.

    Thanks.

  • October 6, 2011 at 6:35 pm

    #1391538

    Well, I re-ordered the SCD select statement and saved/closed/re-opened and it all seems to work properly now with the DBTIMESTAMP.

    just one other of those things where you have to have the exact same column order and perhaps the save/close/reopen trick had something to do with it. But it's weird that with the other column order, the DT_DBDATE worked... strange...

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply