Skip to Content

How to Convert UTC to Local Time in SSIS Package

By: Author Alex Lim

Posted on

Categories Troubleshooting

Learn how to convert UTC to local time in SSIS package using a data conversion transformation and a derived column transformation.

If you are working with data from different time zones, you may need to convert UTC (Coordinated Universal Time) to local time in your SSIS package. This can be useful when you want to display or store the data in a consistent and meaningful way for your users or clients.

How to Convert UTC to Local Time in SSIS Package

In this article, we will show you how to convert UTC to local time in SSIS package using a data conversion transformation and a derived column transformation. We will also provide some references and supplementary materials for further learning.

Problem Description

The problem we are trying to solve is as follows:

  • We have a SSIS package that grabs data from an Informix database and moves it to a SQL Server 2019 database.
  • The datetimes in the Informix database are in UTC but we need them in local (CST) time in the SQL Server database.
  • We just moved this process to a new server and when we try to run the existing package that has a script task to convert the datetimes, we get an error message saying “The binary code for the script is not found”.
  • We have tried deleting and re-adding the script task but it does not work. We want to find a way to do this without using a script task because we think it is more reliable and efficient.

Solution Explanation

The solution we propose is to use a data conversion transformation and a derived column transformation in the SSIS package to convert the UTC datetimes to local time. Here are the steps to follow:

  1. In the SSIS package, add a data conversion transformation after the source component that reads the data from the Informix database.
  2. In the data conversion transformation editor, select the datetime column that you want to convert and change the data type to “database timestamp with precision (DT_DBTIMESTAMP2)”.
  3. In the output alias, enter a new name for the converted column, such as “UTCDateTime”.
  4. Click OK to save the changes and close the editor.
  5. Add a derived column transformation after the data conversion transformation.
  6. In the derived column transformation editor, click the “Add” button to add a new derived column.
  7. In the derived column name, enter a name for the local time column, such as “LocalDateTime”.
  8. In the expression, enter the following formula to subtract the UTC offset from the UTC datetime: 
    DATEADD("hh",-6,[UTCDateTime])

    Note: The UTC offset for CST is -6 hours. You can adjust this value according to your local time zone.

  9. In the data type, select “database timestamp with precision (DT_DBTIMESTAMP2)”.
  10. Click OK to save the changes and close the editor.
  11. Connect the derived column transformation to the destination component that writes the data to the SQL Server database.
  12. In the destination component, map the LocalDateTime column to the corresponding column in the SQL Server table.
  13. Run the SSIS package and verify that the datetimes are converted correctly.

Frequently Asked Questions (FAQs)

Here are some frequently asked questions related to the topic:

Question: How can I convert UTC to local time in SQL query?

Answer: You can use the DATEADD function with the GETUTCDATE function to convert UTC to local time in SQL query. For example, the following query returns the current local time in CST:

SELECT DATEADD(hh,-6,GETUTCDATE()) AS LocalDateTime;

Question: How can I convert UTC to local time in C# script task?

Answer: You can use the ToLocalTime method of the DateTime class to convert UTC to local time in C# script task. For example, the following code snippet converts a UTC datetime to local time and assigns it to a variable:

DateTime utcDateTime = DateTime.Parse("2023-12-12 21:39:59");
DateTime localDateTime = utcDateTime.ToLocalTime();
Dts.Variables["User::LocalDateTime"].Value = localDateTime;

Question: How can I convert UTC to local time in PowerShell script task?

Answer: You can use the ToLocalTime method of the DateTime class to convert UTC to local time in PowerShell script task. For example, the following code snippet converts a UTC datetime to local time and assigns it to a variable:

$utcDateTime = [datetime]::Parse("2023-12-12 21:39:59")
$localDateTime = $utcDateTime.ToLocalTime()
$Dts.Variables["User::LocalDateTime"].Value = $localDateTime

Summary

In this article, we have shown you how to convert UTC to local time in SSIS package using a data conversion transformation and a derived column transformation. We hope this article has been helpful and informative for you. If you have any questions or feedback, please feel free to leave a comment below.

Disclaimer: The information provided in this article is for educational and informational purposes only. We are not responsible for any errors or omissions in the content or for any damages or losses arising from the use of the content. You should always verify the accuracy and validity of the information before applying it to your own situation.