June 24, 2012 at 6:57 pm
Richard,
I have no great love for SSIS and normally avoid it but I have to say you did a nice job on the article. About the only thing that I'd really disagree with is using prefixes on variables to identify the datatype. It's a real pain to change a datatype because, to be true to form, you have to find everywhere it was used in the package and any script code and change it there, as well. I haven't used Hungarian Notation like that in at least 12 years for the very reason stated.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2012 at 11:28 am
Jeff Moden (6/24/2012)
Ok... now you have my curiosity up. How would you schedule for something to run quarterly and guarantee that it schedules its next occurrence? How would you make it obvious that such a self-scheduling feature were obvious without digging into whatever code you have? Me? I'd include such a hint in the name of the job but I'm wondering what else you might come up with in the absence of such a name.And, no... this isn't a challenge to what you said. Since you brought it up, I'd really like to know.
Much like the answer "it depends" - I make no guarantees.
Without a metadata table and without a well-maintained document store, I'd have the naming convention to indicate quarterly job schedule entries. Possibly the naming convention would extend to include "category" so it can easily be sorted/filtered in a jobs listing. Though this turns ugly if/when we try to overload category with the permutations of two or more dimensions (category= Division * Interval [Week|Month|Quarter|Year] * etc)
Upon failures we might have event logging, email operator, etc. - but these don't exactly guarantee the right person will act according to the best laid plans of the package designer. Too much notification and important status is lost in the noise of everyday alerts. Too little notification might also get lost: server-generated email getting clipped by anti-spam due to keywords (for example) - worked well enough with test messages, but without explicitly whitelisting that server the presence of flagged keywords might trash the message. Maybe this could be mitigated by tracking reaction times to event notification, but that also would require discipline and proper metadata storage to mine effectively.
So we're back to my first assumption that without discipline chaos will reign. Obviously the DBA is one with a penchant for order... but how many instances of SQL Server are "maintained" by developers [accidental DBA] rather than dedicated DBA? My original concern was with the "get it done for today" attitude that typically sacrifices out-of-band document management because it takes time to do properly. In that scenario it's often better not to ask the patients to keep the asylum's records because no information is better than misleading information.
Richard addressed my issue by reminding me that no solution would be viable if the work environment isn't stable enough that documentation can be counted upon. The stewards of data should understand that documentation and metadata is also their domain of responsibility. I think ultimately it comes down to the fact that we (and/or the company) must possess a minimum required discipline to be entrusted with the care & maintenance of the corporate database. Any less is an inevitable failure waiting to happen.
August 7, 2015 at 6:24 am
Personally, I like having an extra tool in the box. it might be a way in some edge case of carefully allowing an unprivileged user to schedule a trivial job. Restoring a prod DB to test environment comes to mind.
Other than that, your step eight is precisely the way I used to handle all those notifications. I found a far better way though: add an event handler.
August 18, 2015 at 7:14 am
Great article, thanks.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply