Post reply

Suddenly getting UTC datetime from SSIS System StartTime Variable

  • December 5, 2019 at 2:34 pm

    #3704989

    I have an SSIS package where I have a derived column:

    Load_Dte

    Expression = @[System::StartTime]

    Data Type = date [DT_DATE]

    and it's loading UTC time for this package. Other packages are loading local time EST.

    Thanks!

  • December 5, 2019 at 6:47 pm

    #3705110

    And your question is ...?

    1. Is this a problem?
    2. Can I make all times UTC?
    3. Isn't this great? I thought you'd all like to know.
    4. Do you know why this is happening?
    5. Do you know how I can change it?
    6. Can I make all my packages behave like this?
    7. Should I be worried about my other date columns using other timezones?
    8. something else?

    Just because a question may be obvious to you does not mean it is to everyone else.

  • December 5, 2019 at 6:55 pm

    #3705114

    Sorry Phil.

    My questions are:

    1. Why would I get Load_Dte as local time when run from Desktop and UTC when run from server?
    2. How can I ensure that Load_Dte fed by System:StartTime is local time when run from both Desktop and Server?
    3. Is this a package configuration or a server configuration setting somewhere?

    Thank you.

  • December 5, 2019 at 7:11 pm

    #3705120

    Vertigo44 wrote:

    Sorry Phil.

    My questions are:

      <li style="list-style-type: none;">

    1. Why would I get Load_Dte as local time when run from Desktop and UTC when run from server?

      <li style="list-style-type: none;">

    1. How can I ensure that Load_Dte fed by System:StartTime is local time when run from both Desktop and Server?

      <li style="list-style-type: none;">

    1. Is this a package configuration or a server configuration setting somewhere?

    Thank you.

    Now we're talking! 🙂

    @[System::StartTime] is an SSIS system variable ... it picks up whatever the 'system' tells it and is not user-configurable. So I assume that your server's system time is set to UTC? Your desktop is presumably on local time.

    Or are you saying that you have several packages deployed to the server and that some report @[System::StartTime] in UTC and some in EST? That would require some further thought.

  • December 5, 2019 at 7:30 pm

    #3705123

    Thanks!

    It seems our ETL Server is not on UTC time. Some deployed packages seem to be loading UTC and some Local after the morning ETL loads. Our DBA suspects its patch related. I think it's in his hands now but I am going to check and see if there is any difference with the Derived Column Load_Dte SSIS expression package to package and data type just in case.

  • December 5, 2019 at 7:45 pm

    #3705131

    Please post back when you find out the reason. It's an interesting one.

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

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