How to get achieve getting nextval of sequence from Oracle db, Current date in Eastern Timezone and fire insert statement to the DB with this details

Firthouse M G 40 Reputation points

Hello Team, I have a requirement where I need to fetch the nextval of sequence from on-prem Oracle DB save it in a variable , Get the current time in Eastern time zone and save it in a variable and using this fire a insert statement to update ETL scheduler status in Oracle database table. My ETL Table has ETL_ID - Sequence value from Oracle ETL_NAME - Passed in Insert statement Start_Date - Date stores in variable END_DATE - Null in the insert query Completed - N - flag Once I insert this in DB and run my pipeline after finishing it update the same row with END_DATE - Date stored after pipeline completion and Completed - Y - flag.
How to achieve this in ADF. I was trying with Script and lookup activity but facing issues with variables. My Oracle table has date as data type for date columns. Please suggest best possible way to achieve this.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,039 questions
{count} votes

Accepted answer
  1. Smaran Thoomu 6,110 Reputation points Microsoft Vendor

    Hi @Firthouse M G

    You could try the following steps to address those specific points you've raised.

    • Getting Current Timestamp in Oracle Date Format: Use the following expression in a Set Variable activity to get the current timestamp in Eastern Time Zone and format it as Oracle DATE.
      @formatDateTime(convertTimeZone(utcNow(), 'UTC', 'Eastern Standard Time'), 'YYYY-MM-DD')
    • Using Variables in Insert and Update Statements:
      Enclose variables in @{} within your SQL statements.
      INSERT INTO ... VALUES (@{variables('Seq')}, 'Full Load', @{variables('Start_Date')}, ...)
      UPDATE ... SET END_DATE = @{variables('End_Date')}, ... WHERE ETL_RUN_ID = @{variables('Seq')}
    • Storing Date in a Variable: Use the formatDateTime expression to format the date as needed and store it in a variable.
      @formatDateTime(convertTimeZone(utcNow(), 'UTC', 'Eastern Standard Time'), 'YYYY-MM-DD')
    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful