USE SSIS to update Oracle table column with Null after the value has been assigned in SQL Server to-CodePudding

I have a data table in Oracle that has the following columns:

Record_ID, Run_ID, PO_Type, PO_NUM, DateTime

When a PO is created, all the columns are populated except for Run_ID:

Record_ID, Run_ID, PO_Type, PO_Num, DateTime
---------------------------------------------------
1374,            , NEW_PO , 12345 , 20211117123456
1375,            , NEW_PO , 12346 , 20211117123545

These records are currently exported out of our system via SSIS where they get imported into a SQL Server database. This is where they will be assigned a RUN_ID which will be unique to all of the data runs that were exported (everything that was exported at one time will have the same Run_ID):

RECORDID, SYSTEM, RUN_ID, PO_TYPE, PO_NUM, DATETIME
---------------------------------------------------------
1374,     ORDER , 5078  , NEW_PO , 12345 , 20211117123456
1375,     ORDER , 5078  , NEW_PO , 12346 , 20211117123545

I then need to write back to the Oracle database this Run_ID and update the PO_TYPE from NEW_PO to Processed_PO so my Oracle database would then look like this:

Record_ID, Run_ID, PO_Type      , PO_Num, DateTime
--------------------------------------------------------
1374,     5078   , Processed_PO , 12345 , 20211117123456
1375,     5078   , Processed_PO , 12346 , 20211117123545

The problem I am having is, this all needs to happen within the same SSIS pull, as it is the only tool I have available to me, and I don't know how to begin to tackle this, so any advice on this would be greatly appreciated.

CodePudding user response：

Given your helpful additional information, I understand now that your concern is mostly surrounding making sure that only the rows you extract are the ones you update later with the RUN_ID.

The simplest way I could see doing this is to use the PO_TYPE column and introduce a new status of something like 'PO_Processing'. I don't know your environment / data model so this may or may not be feasible to do - maybe you have limitations to what you can enter here - but the SSIS package steps would then look something like this:

Update the Oracle rows you want

update oracle_table set po_type = 'In_Transit_PO' where <your criteria>

Perform your extract using this status as the selection criteria
Load the data into SQL Server
Store the new RUN_ID in a user variable in the package
Use the user variable to update the SQL Server rows

update SQL_PO set Run_ID = (?) where <your criteria> ('?' maps to your defined package variable)

Update RUN_ID_TRACKER to increment the next RUN_ID
Use the user variable to update the Oracle rows by mapping it (exact syntax may be slightly different depending on which provider your package is configured to use)

update oracle_table set PO_Type = 'Processed_PO', RUN_ID='?' where PO_Type = 'In_Transit_PO'

Done this way, you allow new POs to be generated on the Oracle side while the load is running, but you ensure that only the rows you extracted are the ones you update with the RUN_ID. A couple extra steps in the package but they are each very simple. Not only that, in the event of errors in the process, you have a record of exactly which subset of records it's trying to process, making debugging easier.

Come to think of it you could reduce the steps a bit by obtaining the run_id value and putting it in the variable before your load step, then you already have the value to include when you insert the rows - no need to to a secondary update on the SQL_PO table.