xp_sendmail can handle the e-mail and the DTS package can handle the FTP in
any of several ways. If you want to, you can add a step to the DTS package
to run the standard Microsoft FTP client (which is itself scriptable) or if
you're more adventurous you can write the FTP functions right into the
package using any of those aforementioned ActiveX scripts. Six of one,
half a dozen of the other.
Since stas didn't say whether s/he knows Java or one of the ActiveX
scripting languages, it's hard to know the best solution in that specific
case. In my case, I don't know Java, but I know Perl forwards, backwards,
and inside-out. Since the FTP part of the package doesn't have to scale
like the actual record processing does, I'd probably chain off to the
standard Microsoft FTP client for the download. If for any reason I
couldn't do that, I'd write it right into the package using Perl.
The point is there's more than one way to do it. Use the one that works
best for that specific scenario. Stas asked if DTS would be able to take
care of it. The answer is yes - that's what Microsoft built it for.
Whether it's the *best* tool is left as an exercise to the reader. QED.
|------------------------+------------------------------------------------|
|Eric A. Laney |"Nothing great was ever achieved without |
|Systems Engineer |enthusiasm." |
|LAN Optimization Team | |
|Verizon Data Services | |
|Voice: 813.978.4404 | Ralph Waldo Emerson|
|Pager: 888.985.8519 | |
|------------------------+------------------------------------------------|
travis@thinkv
irtual.com To: SQL <[EMAIL PROTECTED]>
cc:
2001-12-13 Subject: RE: RE: Re: Can I use DTS for
this?
16:00
Please
respond to
sql
True, but if we want to do all the steps:
1. Grab the file.
2. Drop existing records from a table.
3. Re-populate the table with new data
4. Send success or failure message by e-mail
then at least for 1 and 4, you need some program to do it. So this is
where a nice little java program could automate the whole process for you.
Travis
---- Original Message ----
From: [EMAIL PROTECTED]
Sent: 2001-12-13 13:33:21.0
To: SQL <[EMAIL PROTECTED]>
Subject: RE: Re: Can I use DTS for this?
That's certainly true, but you'll see much better performance if you let
the database handle it natively instead of mucking about in the middleware
layer. ColdFusion is an excellent product, but it is unlikely to be the
best tool for this job from a performance perspective. I don't have any
experience with JDBC, but I also doubt that it will be as efficient as the
native drivers used by DTS.
The primary issue sounded to me like a concern for scalability, which in my
mind precludes Java or ColdFusion.
|---------------+---------------------------------------------------------|
|Eric A. Laney |"Since wars begin in the minds of men, it is in the minds|
|Systems |of men that the defenses of peace must be constructed." |
|Engineer | |
|LAN | |
|Optimization | UNESCO charter|
|Team | |
|Verizon Data | |
|Services | |
|Voice: | |
|813.978.4404 | |
|Pager: | |
|888.985.8519 | |
|---------------+---------------------------------------------------------|
travis@thinkv
irtual.com To: SQL
<[EMAIL PROTECTED]>
cc:
2001-12-13 Subject: RE: Re: Can I use DTS
for this?
15:01
Please
respond to
sql
You could use any kind of programming to do this as well... Like java or
cold fusion even. Just read in the file, parse it out, check for errors
and do inserts.
Travis
---- Original Message ----
From: [EMAIL PROTECTED]
Sent: 2001-12-13 12:21:18.0
To: SQL <[EMAIL PROTECTED]>
Subject: Re: Can I use DTS for this?
With a little elbow grease, DTS would be perfect for this. You can use any
ActiveX script language (VBScript, JScript, and PerlScript are the three I
remember) to validate the data and choose whether to push the record to the
database, skip the record, or error out the entire DTS process. You can
process each record in one big block, or one field at a time, or any
combination of these.
Like most really flexible, powerful systems, it is complex and
intimidating. However, it is an excellent way to accomplish your goal.
|-------------------+-----------------------------------------------------|
|Eric A. Laney |Opera is when a guy gets stabbed in the back, and |
|Systems Engineer |instead of bleeding, he sings. |
|LAN Optimization | |
|Team | |
|Verizon Data | |
|Services | |
|Voice: 813.978.4404| |
|Pager: 888.985.8519| |
| | |
|-------------------+-----------------------------------------------------|
"stas"
<stas@newdel. To: SQL
<[EMAIL PROTECTED]>
net> cc:
Subject: Can I use DTS for
this?
2001-12-13
14:03
Please
respond to
sql
Here is the scenario:
A text file is made available to us on a weekly basis for FTP download.
1. Grab the file.
2. Drop existing records from a table.
3. Re-populate the table with new data
4. Send success or failure message by e-mail
It looks to me like a DTS package should be able to take care of this,
however I've never used it. My main concern is that the text file will
contain about 10,000 records and not all data will be clean. We need to
skip
over bad records and continue to the end of the file.
Thanks!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
