Saturday, June 1, 2013

SSIS: Time zone conversion

I needed to convert date/time data within a SSIS data flow from US Central Standard Daylight Time (UTC-5:00) to my local timezone which could vary between AU Central Standard Time and AU Central Standard Daylight Time (UTC+9:30 or UTC+10:30). The source data was guaranteed to always be UTC-5:00, so I only needed to allow for the potential variance for AU daylight savings.

I used a SSIS script transform to achieve the desired result:



And the key code being contained in the ProcessInputRow method of the script transform:

public override void Input_ProcessInputRow(InputBuffer Row)
{
  Row.PurchaseDateLocalTime = TimeZoneInfo.ConvertTimeBySystemTimeZoneId(

    new DateTimeOffset(
      Row.PurchaseDate,
      new TimeSpan(-5, 0, 0)),
    "Cen. Australia Standard Time")
  .DateTime;
}

No comments:

Post a Comment