Timezone conversion in SSIS

Question

Timezone conversion in SSIS

H4K

Hall of Fame

Points: 3374
More actions
May 4, 2021 at 7:06 pm

#3879302

Hey Experts,
I need help with timezone conversion in SSIS ETL packages consistent with what a javascript npm package given below:
https://www.npmjs.com/package/date-fns-tz
How can use npm package in SSIS ? Is that possible? if not, is there any other alternative?
I know about "AT TIME ZONE" in SQL Server but since it has severe performance issues I don't want to use it.
The main issue is I have to be consistent with what npm package used which supports IANA timezone codes.

Regards,
Amar
BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI

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

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

frederico_fonseca SSCoach Points: 16014 More actions · Answer 1

you can code it in a c# script within ssis - most likely there are already functions to do that including .net native ones.

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 2

I am curious - why do you need to convert the data in SSIS? What is the format of the data - and what is the desired result? Depending on how the data is structured - you may just need to store it in the datetimeoffset data type instead of trying to 'convert' it.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

H4K Hall of Fame Points: 3374 More actions · Answer 3

Thank you for replying.

@Jeffrey Williams -

So the datetime data in transactional DB system is always stored in UTC. This datetime data is then converted in whatever timezone the user wants on front-end using that npm package.

@frederico_fonseca -

I don't think there is C# code for timezone supporting IANA timezone names. If there is please send some details, will appreciate it.

This reply was modified 2 years, 11 months ago by H4K.

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI

frederico_fonseca SSCoach Points: 16014 More actions · Answer 4

easy search with google (https://www.google.com/search?q=IANA+timezone+names+c%23) and the following is one of the first hits.

https://github.com/mattjohnsonpint

there may be others

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 5

H4K wrote:

Thank you for replying.
@Jeffrey Williams -
So the datetime data in transactional DB system is always stored in UTC. This datetime data is then converted in whatever timezone the user wants on front-end using that npm package.
@frederico_fonseca - I don't think there is C# code for timezone supporting IANA timezone names. If there is please send some details, will appreciate it.

So is it stored as a datetime data type? Where are you getting the offset for the time zone - to be able to 'convert' it to the appropriate local time?

You want to be able to convert it in SSIS - assuming you want to convert it from UTC to some local time? Which really means you lose information on the destination - and most likely have already lost information when storing the data, unless you are storing the offset in the database somewhere.

What is the data type of the column you are extracting? And why do you thing using AT TIME ZONE in SQL won't work?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs