|
Home > Archive > MS SQL Server DTS > July 2005 > Mailing from DTS
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Blasting Cap 2005-07-20, 11:23 am |
| 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(strSourceP
ath)
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
| |
| Narayana Vyas Kondreddi 2005-07-20, 1:23 pm |
| 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@christian.net> wrote in message
news:%23XZGwsTjFHA.576@TK2MSFTNGP15.phx.gbl...
>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(strSourceP
ath)
> 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
>
| |
| Darren Green 2005-07-20, 8:24 pm |
| 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
| |
| Blasting Cap 2005-07-20, 8:24 pm |
| Narayana Vyas Kondreddi wrote:
> 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.
How do you then work the xp_sendmail stored procedure to pull in your
variables as parameters?
I have:
xp_sendmail @recipients='me@anyw
here.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
| |
| Blasting Cap 2005-07-20, 8:24 pm |
| Darren Green 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.
>
>
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
| |
| Darren Green 2005-07-20, 8:24 pm |
| In message <#NPMIaWjFHA.708@TK2MSFTNGP09.phx.gbl>, Blasting Cap
<goober@christian.net> writes
>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
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
| |
| Blasting Cap 2005-07-20, 8:24 pm |
| Darren Green wrote:
> In message <#NPMIaWjFHA.708@TK2MSFTNGP09.phx.gbl>, Blasting Cap
> <goober@christian.net> writes
>
>
>
> Changing the DTS Send Mail Task
> (http://www.sqldts.com/default.aspx?235)
>
This was exactly what I had tried. I had the following in my DTS job:
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\myfilenam
e.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_DTSSendMailT
ask_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
| |
| Darren Green 2005-07-20, 8:24 pm |
| In message <usMOflWjFHA.3784@tk2msftngp13.phx.gbl>, Blasting Cap
<goober@christian.net> writes
>Darren Green wrote:
>This was exactly what I had tried. I had the following in my DTS job:
>
>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\myfilenam
e.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_DTSSendMailT
ask_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
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
| |
| Blasting Cap 2005-07-21, 9:23 am |
| > 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.
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
| |
| Darren Green 2005-07-21, 8:24 pm |
| In message <u0j3LBgjFHA.1148@TK2MSFTNGP12.phx.gbl>, Blasting Cap
<goober@christian.net> writes
>
>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
>
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
|
|
|
|
|