Update an Imported_Indicator column Source table

• Michael Gerholdt

  SSCommitted

  Points: 1907

  More actions

  November 4, 2014 at 11:34 am

  #312234

  I have an SSIS package which copies data from Oracle to an SQL Server 2008 R2 DB and no problems there.

  What I want is to add a column to the source table in Oracle which will be a single character. New records will go in with an "N" value. I want the SSIS package to pull only records that have an "N" value in that column ... and then flip it to "Y" to show that this record has been copied over.

  I need to avoid the "mutating table" problem in Oracle. I can see a sort of long-hand solution - have a separate table in Oracle, insert values there, and then update the values in original table using the second table as source, then truncate the second table.

  There must be a more "SSIS" based way to record those value in a logical in-memory table and then essentially do the same update from there once the initial transfer is accomplished.

  Can someone point me at the way to do this? Mostly, just storing one value from each record in a logical table, then iterating through that to pull each value into a parameter, do the update, go next.

  Thanks -