I am sending two text files via FTP to a remote server in a DTS job.

The filenames will vary in name each time they're created. They'll be
named something like the long date name + the word summary or detail
right before the file extension.

What I want to do is when both of these are sent, I would like to be
able to send an email to me and the other folks needing to know about
the new file that was just sent.

I would like to do the following:

1. In the subject I would like to say something like "2 files sent,
MM/DD/YYYY" where the mm/dd/yyyy would be the same as the date of the
transfer.

2. Within the body of the email, I would like to reflect what the
filenames were, as well as the date & time they were sent to the remote
FTP.


The dates are not a "gotta have" type of thing, but they would be nice.
I do though want the filenames within the body of the email.

I am creating the ftp send thru an ActiveX script:

Set objTxtFile = fso.CreateTextFile( strFTPPathName & "\ftpSend.txt", TRUE)

' Write the Header
objTxtFile.writeline( "open ftp.remotesite.net" )
objTxtFile.writeline( "username" ) 'username
objTxtFile.writeline( "password" ) 'password

Set folder = fso.GetFolder(strSourcePath)
Set filecollection = folder.Files

For each file in filecollection
objTxtFile.writeline( "put """ & strSourcePath & "\" & file.name
& """")
Next

objTxtFile.writeline( "quit" )
objTxtFile.Close


When this job finishes, I then call the ftp.exe program with the
following parameters -s:"myserver\ftpSend.txt"


We already have SQL mail working on this box, without any issues. I've
seen some things out there where there's a third party add-on for
sending sql mail, but it doesn't give you much flexibility in changing
on the fly things like the run date and such.

Any ideas how to go about doing this? Any suggestions appreciated.

BC

Not sure I follow you - Could you simply use a variable for the subject
parameter of the 'send mail' stored procedure? This variable could be set to
any value you like.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/


"Blasting Cap" <goober (AT) christian (DOT) net> wrote

Whilst building stuff in the script to support the FTP, build your mail
subject and body strings and store them in a global variable for later
assignment, or just set them directly onto the Send Mail Task from
within the script.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Narayana Vyas Kondreddi wrote:
How do you then work the xp_sendmail stored procedure to pull in your
variables as parameters?

I have:

xp_sendmail @recipients='me (AT) anywhere (DOT) com',
@subject=? + ' Files sent to vendor ',
@message='Sent on ' + ?

where the first (?) would be the number of files sent, and the second
(?) would be the date on which the files were sent.

When I code this, and hit the parameters button in Execute SQL Task
properties, it gives me an error, " Syntax Error or Access Violation "
and that an error occurred when parsing the sql for parameters.

BC

Darren Green wrote:

I can't figure out how to set them in a variable name that'll get called
by the Send Mail task, or be recognized when trying to send them via the
xp_sendmail routine.

I tried something like this the other day with an activex script between
the ftp and the Send Mail task, that when the subect would get set on
the subsequent send mail task, it'd say it couldn't find the send mail
task.

BC

In message <#NPMIaWjFHA.708 (AT) TK2MSFTNGP09 (DOT) phx.gbl>, Blasting Cap
<goober (AT) christian (DOT) net> writes
Changing the DTS Send Mail Task
(http://www.sqldts.com/default.aspx?235)

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Darren Green wrote:

1. An activex script that formatted the FTP command and set a global
variable, say for the number of files.

2. On success of #1, it ran an application, in this case, FTP.exe,
calling a parameter -s:"\\myserver\myfilename.txt" that had the ftp
commands in it.

3. On success of #2, it went to another activex job, which I took
verbatim from that page, http://www.sqldts.com/default.aspx?235

4. On success of #3, it called the Send Mail Task.

When I tried to run it, it would give me a message in this line in the
code you referenced at sqldts:

' Get DTS Send Mail Task by Name
Set oSendMailTask = oTasks("DTSTask_DTSSendMailTask_1").CustomTask

telling me that it couldn't find the name. I went to the Send Mail Task
right clicked and went to WorkFlow Properties, and then to Options, and
copied exactly the same name, and put in that step. Still the step 3
said it could not find the DTS SendMail Task1. It would never get to
the Send Mail task, but would die on the line above. I could never
figure out what the problem was with it, since I knew that the step name
was correct.

BC

In message <usMOflWjFHA.3784 (AT) tk2msftngp13 (DOT) phx.gbl>, Blasting Cap
<goober (AT) christian (DOT) net> writes
It sounds like you had the wrong task name. Right-click, Workflow
properties gives you the step name. You need the task name. Normally you
can just change the DTStep* for DTSTask* to get the value, or use
Disconnected Edit to check it out.

Steps and tasks are closely related (see TaskName of Step object), but
they are not the same. They are just close enough to be confusing! The
former is used for workflow, execution control if you like, the latter
does the actual work.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

I found the problem - there was a typo in what I pasted into it.
Evidently, I had left an "o" out of something.

The mail is now working - sort of....

On this system, I have Outlook and am signed in to it using the account
that SQL is logged in on, and have been testing the code thus far. The
mail is formatting fine, but from what I've found, it doesn't send them
properly.

I was testing yesterday afternoon once I finally got it to work, and
would get emails bunched together. Testing over about a 10 minute span,
I would get the emails within seconds of each other. One never showed
up until I logged in this morning. It suddenly appeared in the sent
items folder of Outlook, and showed up on my email account.

I have checked the settings on outlook, and it's set to send mail
immediately, but for some reason they're not showing up in what I would
consider a reasonable amount of time (say within 5-10 minutes of
execution of the job).

Is there any way to troubleshoot the way that sql & outlook communicate
with one another?

BC

In message <u0j3LBgjFHA.1148 (AT) TK2MSFTNGP12 (DOT) phx.gbl>, Blasting Cap
<goober (AT) christian (DOT) net> writes

The stock Send Mail task uses MAPI, just like SQL Mail and SQL Agent
Mail. MAPI is the spawn of the devil. It is really a desktop orientated
technology which is not designed for unattended execution. It is owned
by Office, and they have different priorities compared to the
requirements for server systems, which means you are lucky if it works!

I use xp_smtp_sendmail for all my notifications. In DTS I'd use an Exec
SQL Task, and feed it through there. (See http://ww.sqldev.net). Gert
also has a a SMTP Send Mail Task whch you may prefer, I just haven't
adopted since I already have myself setup quite nicely with the xp.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org