August 16, 2018 at 12:45 pm
One way I do this is use a Recordset Destination (ensure the variable you are populating is an Object.
August 16, 2018 at 12:49 pm
Chris Hurlbut - Thursday, August 16, 2018 12:45 PMOne way I do this is use a Recordset Destination (ensure the variable you are populating is an Object.
Can you expand on this? How do updates get from the recordset to the target database?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 16, 2018 at 12:52 pm
^+1 to that question. The suggestion sounds like it might be perfect for me but there's bits in there I'm not sure how to do - ie, how to push the state of the object into the database. Again, I guess I could do it with ForEach containers wrapping Update Execute SQL tasks but that's going to get pretty complex given the number of fields involved.
August 16, 2018 at 1:15 pm
For what it's worth, the "right to forget" in the GDPR does not apply if there are other legal requirements to retain the data - Article 17, paragraph 3 (b). https://gdpr-info.eu/art-17-gdpr/
August 16, 2018 at 1:17 pm
Or you could use a SQL Command as destination. If the rows are going to be less than 50 at a time, it might be an option.
August 16, 2018 at 3:21 pm
For what it's worth
Thanks, that's interesting. I'll pass it to the security expert and maybe that'll let us bypass this requirement. It's not my call to make, though, so for now I'm going to assume I've got to fulfil it.
Or you could use a SQL Command as destination. If the rows are going to be less than 50 at a time, it might be an option.
I wasn't aware of that capability. That's definitely worth me taking a look at . Thanks.
August 16, 2018 at 9:49 pm
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:
Since they "improved" the site more than a year ago, the forums have been a train wreck compared to what they were before, so you're not alone in the weirdness.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 16, 2018 at 9:52 pm
Shifting gears on all of this a bit... the "lesser" site (the one that does NOT have the forgotten data) should not be doing the data "pull", IMHO. To be secure, only people or code that's authorized to access the "forgotten" data should do a PUSH to the lesser site. There's a huge advantage in that you CAN have a one way linked server then. Solves a shedload of problems.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2018 at 12:28 am
you're not alone in the weirdness.
That's both a relief and not. Nice to know I'm not alone but, egad, it's painful to post right now.
There's a huge advantage in that you CAN have a one way linked server then. Solves a shedload of problems.
Yeah. I did suggest that up front but I was told no. I think I'll float it again though because it would massively simplify all this. I suspect what's really happening is that the company are being way over cautious until there's some case law out.
August 17, 2018 at 2:13 am
I did this once
Execute SQL Task to create global temp table
Data Flow Task
…....OLE DB Source
…….OLE DB Destination for the output temp table (make sure you set ValidateExternalMetaData to False)
Execute SQL Task to update destination permanent table.
Far away is close at hand in the images of elsewhere.
Anon.
August 17, 2018 at 4:19 am
^That's the approach I started out with but I came unstuck when setting the temp table as the destination. Temp tables (global or local) just aren't available in the target selection list. I probably could have dealt with that by using an expression somewhere in the task or the connection manager but an OLEDB destination requires that the columns are mapped and there was no way into the mappings tab without the table being selected. In turn I could probably have gotten around that by either setting everything up manually in the Advanced Editor or by mapping to a permanent "dummy instance" of the table at design time and then repointing the package to the temp instance at run time but I wasn't sure what unexpected obstacle that might throw up.
For now I'm getting it working with permanent tables (should be done by the end of the day) so I know I've got a working solution in place but I really want to revisit over the next couple of weeks and try out some of the ideas in this thread. There's been some good stuff here that deserves a crack.
August 17, 2018 at 6:31 am
andycadley - Thursday, August 16, 2018 1:15 PMFor what it's worth, the "right to forget" in the GDPR does not apply if there are other legal requirements to retain the data - Article 17, paragraph 3 (b). https://gdpr-info.eu/art-17-gdpr/
That's a good link, Andy. Thanks for posting it.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2018 at 10:13 am
FunkyDexter - Friday, August 17, 2018 4:19 AM^That's the approach I started out with but I came unstuck when setting the temp table as the destination. Temp tables (global or local) just aren't available in the target selection list. I probably could have dealt with that by using an expression somewhere in the task or the connection manager but an OLEDB destination requires that the columns are mapped and there was no way into the mappings tab without the table being selected. In turn I could probably have gotten around that by either setting everything up manually in the Advanced Editor or by mapping to a permanent "dummy instance" of the table at design time and then repointing the package to the temp instance at run time but I wasn't sure what unexpected obstacle that might throw up.For now I'm getting it working with permanent tables (should be done by the end of the day) so I know I've got a working solution in place but I really want to revisit over the next couple of weeks and try out some of the ideas in this thread. There's been some good stuff here that deserves a crack.
First right click on the Execute SQL Task that creates the temp table and execute it (this will create the global temp table.
Next view the properties for OLE DB Destination and type the temp table name in the openrowset property
Double click on the OLE DB Destination and then you can do the mapping
Far away is close at hand in the images of elsewhere.
Anon.
August 17, 2018 at 4:28 pm
Heh... or just do it all in T-SQL. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2018 at 2:35 am
type the temp table name in the openrowset property
Aha! I didn't spot that you could type directly in on the property pane (The pop out properties window just has a non-typeable dropdown and I assumed the properties pane would be the same - should have checked really). That's certainly closer to what I want than using permanent tables so I'll be implementing it today. Thanks:)
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply