Timezone conversion in SSIS

  • 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

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

  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • 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 days, 12 hours 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

  • 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

  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

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

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