Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lfmn
Posting Yak Master
141 Posts |
 Posted - 2002-12-18 : 12:30:14
|
| I've done a search on this issue, but haven't found an answer. I have three questions and would like to know if anyone has a link to the answers:We just upgraded to SQL 2000 running on NT enterprise serverUsing DTS:1) Can I receive an email, parse the body of the email and insert the results into a database2) Can I receive an email with an attachment, parse the body of the attachment and insert the results into a database3) Can I connect to an FTP site, collect the files, parse them and insert them into a database (number and name of files vary)Thanks for your help.I also looked at Professional SQL Server 2000 DTS, but the book doesn't adress the email issue and has very little on FTP.SQL is useful if you don't know cursors :-) |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-12-18 : 13:19:02
|
| Looking through the dts tasks there doesn't seem to be a email read task but you can call a stored procedure which does it.1) should be relatively easy with xp_readmail2) should be less easy with xp_readmail3) can probably try the ftp task but people have had problems with that and resorted to calling a com object or such to do the work.Why do you want to do this using dts?>> SQL is useful if you don't know cursorsBut also if you do :).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 12/18/2002 13:20:13 |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-12-18 : 14:11:17
|
| I could not get the ftp task to do what I wanted, so I ended up creating a cmd file that does what I need. I launch the cmd file in the DTS package through xp_cmdshell. The part that I couldn't get to work was the fact that when you setup the task, you have to know what the file names are going to be. Well in my situation, I won't know that, so it is impossible to set it up. |
|
|
|
lfmn
Posting Yak Master
141 Posts |
Posted - 2002-12-18 : 15:34:31
|
quote:
Looking through the dts tasks there doesn't seem to be a email read task but you can call a stored procedure which does it.1) should be relatively easy with xp_readmail2) should be less easy with xp_readmail3) can probably try the ftp task but people have had problems with that and resorted to calling a com object or such to do the work.Why do you want to do this using dts?>> SQL is useful if you don't know cursorsBut also if you do :).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 12/18/2002 13:20:13
We are currently processing this data via visual basic programs and would like to use dts to make them more robust. I'm open to other suggestions.The first visual basic program goes to the FTP site, grabs the file and parses it, then inserts into the database.The emails come into outlook where a macro puts either the email or the attachment into a subdirectory where a VB program parses it and inserts it into a database.I thought it would be more efficient to use SQL Server to process this data where possible.SQL is useful if you don't know cursors :-) |
|
|
|
|
|
|
|
|
DTS Questions re: ftp and email