We have to encrypt sensitive data in the etl stream, using a global temporary table as a "staging" table provides session local exposure of the data, I would like to load or update into the global temp table, then insert into the encrypted table via a view on the global temp which applies the encryption udfs.
This works nicely because if the session crashes, the sensitive data is "lost" when the session ends, and we don't run the risk of leaving sensitive data anywhere.
But...tpt won't allow the use of a global temp table as a target...are there any other alternatives?
How big is your data set? There's always the option for a BTEQ load but that obviously has downsides for a large data set. Other than that, I'm really drawing a blank on other options. As far as I can remember, none of our bulk load utilities allow for writing to a global temporary table. And with the data being private to the session, all of your work would have to be done using the exact same connection.
I guess you could write an INMOD for your load utility that would do the encryption there, but I'm not very familiar with INMOD coding so I don't know exactly how complicated that would be.