none
SSIS: DateTimeOffset column issue RRS feed

  • Question

  • I'm designing a SSIS package that pushes delta records from one SQL DB to another using Script Task. I've a control flow with Execute SQL task and Script task. Execute SQL Task reads data from source database which is inserted/updated after last successful run date. I'm storing last successful run date in a XML file on file server. After reading data from source DB it uses Script Task to push data through another application database using web services. After successful run, I update the Last Successful Run date in XML file which will be read when the SSIS package runs next time using SQL Agent Job. Last Successful Run Date is being updated in XML in "dd/MM/yyyy hh:mm:ss tt" (03/27/2011 10:10:15 AM) format. My SQL Task has query like..

    SELECT col1, col2 FROM Table1 WHERE InsertDate > @LastSuccessfulRunDate OR UpdateDate > @LastSuccessfulRunDate.

    InsertDate and Update columns are of type DateTimeOffset(7). When I run this query I'm not getting any records even if the records are inserted/updated after given time.

    Anyhelp would be appreciated.

    Thanks 

    Sunday, March 27, 2011 12:34 PM

Answers

  • You say you're using DateTimeOffset... but you're not specifying any timezone?  It's quite possible then that it's interpreting the time you're passing to it as a GMT time, which won't give you the results you're expecting if your database times are in another timezone.
    Todd McDermid's Blog Talk to me now on
    Monday, March 28, 2011 4:07 PM
    Moderator

All replies

  • before query you should set the date format

    Like this

    Set dateformat dmy

    SELECT col1, col2 FROM Table1 WHERE InsertDate > @LastSuccessfulRunDate OR UpdateDate > @LastSuccessfulRunDate.

     

    I hope this should solve the issue for more here http://msdn.microsoft.com/en-us/library/ms189491.aspx

    Sunday, March 27, 2011 12:38 PM
  • You say you're using DateTimeOffset... but you're not specifying any timezone?  It's quite possible then that it's interpreting the time you're passing to it as a GMT time, which won't give you the results you're expecting if your database times are in another timezone.
    Todd McDermid's Blog Talk to me now on
    Monday, March 28, 2011 4:07 PM
    Moderator