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 TimeSpan(-5, 0, 0)),
"Cen. Australia Standard Time")