November 27, 2009 at 11:24 am
Background:
I maintain a fairly simple database of membership applications generated from my company's website. We have leads driven to us from numerous web affiliates who require a list of applications on a weekly (sometimes twice a week) basis stating which ones we have approved/rejected. I simply send them a csv file with a list of each application and its status.
For each affiliate, I have created a view that only shows applications that have passed through screening and for a specified time frame. I run an SSIS package that exports the data from the view into a csv file and then run an sql script that emails it to the affiliate.
Question:
Is it standard practice or even an acceptable method in cases as I've described to create a a new view for each new affiliate we send files to? Or would a different method serve as a better solution, such as SP's or run the script in an SSIS data flow component or something I'm just not familiar with?
All this said, I'm not having any issues doing it the way I am. I'm just curious if I'm overlooking a better solution and would be better off learning a new method.
-Jeff
November 27, 2009 at 2:26 pm
What is better?
More robust? I'd say more robust is always better.
Faster? maybe, I am going to improve the efficiency of a 30 second procees that runs on the overnight on a server that is not being used at night. Probably not.
Capable of being automated? This is good depending on how you value your time.
Capable of being monitored? Unmonitored automation is dangerous.
Ongoing maintenance? Again this depends on the availability of your time.
Understandability? How easy is it to fix for someone walking in blind?
For me even if it took a minimal amount of time per week I would go with an automated, monitored, documented process.
Here's the outline of one process, I'm sure there are many.
-write a select that will select members that need an email
-put this in a cursor; some may say a cursor is bad form, but for triggering an external process(SSIS) I think its OK
-build a string dynamic sql string that uses dtexec to call a generic package; use package variables to pass the member code to the package
-execute the package
-check and log the return code of the package
-check and log the number of records inserted into a file
-archive and datetime stamp your files
-if successful send the email
-use try and catch to look for and log errors durring the whole process
-send a success/failure email to yourself (and your backup so you can take a vacation)
-write documentation
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply