Blimey, loads of stuff to respond to. Thanks for all the input folks.
A bit of background that might help (I didn't include it before because it doesn't really inform the question at hand but might help answer some of the questions you guys have asked). GDPR means we have to "forget" a customer if they ask us to. Ie remove all personal information from the system. But we're also subject to legislation that means we have to retain data for a certain length of time (2 years if no contract was formed, 5 years if it went to contract) for use in legal disputes, fraud investigation etc. So we've got a conflict there.
The solution is that, when a customer asks to be forgotten, we copy their personal data into a "secret" (not really the right word but it'll do) repository that 's locked down and isn't readily accessible by anyone, even the DBAs. For anyone to access it they need to have gone through a process that audits the legitimate need etc. All access is audited and, if someone could prove we accessed it without a legitimate need we would be in breach of GDPR. Their data is then redacted in the main system. These two system need to sit on separate servers.
All that's working fine but when there is a legitimate need to access the "forgotten" data, we need a process to recover the data from the repository into the main system while the dispute/fraud etc is investigated and resolved. That's what I'm working on right now.
What sort of 'collision' are you concerned about?
Just that if more than one person runs the process at the same time one might drop and replace the table while the others using it. That sort of thing. If I chuck some sort of unique identifier in the table name I can work around that though. Also, this recovery process will probably need to be run once in a blue moon so I'm probably worrying over nothing.
You can if you do it all in a stored procedure.
The logic's easy enough, it's the access that's the problem. I can't think of away I can do it in pure sql without a linked server. Am I missing something there?
don't the necessary connections you've created for SSIS violate the very rules of not having permanent connections that rule out such things as Linked Servers???
Well I'm told not. I don't proclaim to be an expert on the legislation, though, and am working on the advice from our security expert.
you create a "Scratch" database create them in rather than using TempDB[/quote]Yeah, we've got a "DBA workhorse" database I can use. It's got no access at all outside of the DB team so should be isolated enough.
The connections wouldn't be permanent. They would only exist while the package is running. They'd also be done by a different server working as a bridge between both instances.[/quote]I believe that sums up the legal situation, yes. Like I said, though, I'm just doing what our legal champ says on this though.
As an aside, I'm getting some really odd problems posting in the last couple of days. The first letter of each paragraph keeps getting shoved along, and right now it looks like the colours are spontaneously changing for no obvious reason (I've got blues, blacks and now a weird orange:unsure:). Is there a bug reporting mechanism on this forum? I couldn't see one.
Edit> and apparently the quote tags aint working for me either:doze: