If I was starting from scratch I'd probably have done the same but all the data handling (which is waaaaaay bigger and more complex than I've portrayed here) was already in place using a pure sql agent job and I didn't really want to start breaking that out to a new package.I would have done the whole thing in an SSIS PAckage
Also, this client doesn't really have a proper (or any) development process for DB stuff so the source of the package would probably just have been saved off to some folder somewhere and promptly forgotten about... right up until they wanted some critical maintenance and couldn't remember where it was. Keeping it directly in the job would have been simpler.
We're going to maintain a table of "files to be deleted" which we populate, along with a delete date, at the point of copying the records into the parallel system. That will be trivial to add to the existing agent job (in fact, I've already done it). Then the app development team are going to develop a small service to sit on the file server, interrogate the table and delete the files at the appropriate time. Because they've got better practices in place I'm more confident that that wont go walkaboutI'm curious though, you said you went a different route - what was it?
A bit of me would still like to have kept the whole lot under one roof but, the more we discussed it, the more we realised that the tasks don't really synch up with each other anyway (e.g. we realised we don't need a parallel structure for the documents because they'll be unreachable with the records anyway). I'm pretty sanguine with where we've ended up.





Reply With Quote