如何将SSIS中@[System::ContainerStartTime]转换为UTC时间？

Great question! The @[System::ContainerStartTime] variable returns the local datetime of the server running your SSIS package, and since SSIS doesn’t have a built-in expression to convert this to UTC directly, here’s how to do it reliably using .NET’s time zone handling (which automatically accounts for daylight saving time changes):

Method 1: Use a Script Component (Data Flow Transformation) #

This is the best approach if you need the UTC time as a column in your data flow:

1. In your Data Flow, add a Script Component and select Transformation as the component type.
2. Connect your existing data source to this Script Component.
3. Open the Script Component editor:
   • Go to the Inputs and Outputs tab. Expand Output 0, click Add Column, name it something like ContainerStartTimeUTC, and set its data type to DT_DBTIMESTAMP (matches SSIS’s datetime type).
   • Switch to the Script tab. Under ReadOnlyVariables, add @[System::ContainerStartTime] (select it from the dropdown to avoid typos).
   • Click Edit Script to open the VSTA editor.
4. Replace the default Input0_ProcessInputRow method with this code (C# example):

   public override void Input0_ProcessInputRow(Input0Buffer Row)
   {
       // Grab the local start time from the SSIS system variable
       DateTime localStartTime = (DateTime)Variables.ContainerStartTime;
       // Convert local time to UTC using .NET's built-in time zone logic
       DateTime utcStartTime = localStartTime.ToUniversalTime();
       // Assign the UTC value to your new output column
       Row.ContainerStartTimeUTC = utcStartTime;
   }
   

5. Save the script and close the editor. When your Data Flow runs, the new column will hold the UTC equivalent of the ContainerStartTime.

Method 2: Use a Script Task to Set a UTC Variable (for use outside the Data Flow) #

If you need the UTC time stored in an SSIS variable (e.g., to use in an Execute SQL Task), follow these steps:

1. Create a new SSIS variable (e.g., User::ContainerStartTimeUTC) with data type DateTime.
2. Add a Script Task to your Control Flow.
3. Open the Script Task editor:
   • Under ReadOnlyVariables, add @[System::ContainerStartTime].
   • Under ReadWriteVariables, add @[User::ContainerStartTimeUTC].
   • Click Edit Script.
4. Use this code in the Main method:

   public void Main()
   {
       DateTime localStartTime = (DateTime)Dts.Variables["System::ContainerStartTime"].Value;
       DateTime utcStartTime = localStartTime.ToUniversalTime();
       Dts.Variables["User::ContainerStartTimeUTC"].Value = utcStartTime;
       
       Dts.TaskResult = (int)ScriptResults.Success;
   }
   

5. Save and run the task. Your variable will now hold the UTC time.

Key Notes: #

• Time Zone Context: ToUniversalTime() uses the local time zone of the machine running the SSIS package, which matches the time zone of ContainerStartTime—so this conversion is accurate.
• Avoid Hardcoding Offsets: Don’t use a Derived Column with a fixed offset (like DATEADD(hour, -5, @[System::ContainerStartTime])). This will fail to account for daylight saving time changes and be incorrect for part of the year.

内容的提问来源于stack exchange，提问作者Dennis Guse