Naming a File and E-mailing Using DTS

Question

Post reply

Naming a File and E-mailing Using DTS

tygun

SSCrazy

Points: 2381
More actions
March 16, 2004 at 6:55 am

#60063

I have a DTS Package that exports data from a table to a fixed length file. I want this DTS Package to have the intelligence to;
1. Name the file witht the current data and then a string concatenated to it.
2. E-mail the file, once created to a few people.

I believe that I probably need to utilize the ActiveXScript to control this, but quite honestly I'm a bit confuse with it.
Could somone please let me know the process I need to follow to make this happen.
Thanks in advance.

st

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

FilesystemObject can do the rename.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vtoriVBScript.asp

Include this in an active X script.

For the email, I use ASPEmail from persits (http://www.aspemail.com/) with this code:

Function Main()

Set Mail = CreateObject("Persits.MailSender")

Mail.Host = "mail.mydomain.com"

'put the webmaster address here

Mail.From = "webmaster@mydomain.com"

'The mail is sent to the address entered in the previous page.

'Mail.AddAddress "sjones@sqlservercentral.com"

end if

'Enter the subject of your mail here

Mail.Subject = "Current Schedules"

'This is the content of thr message.

Mail.Body = "Here is all the current Schedules for as of " & now

' Add the attachment

Mail.AddAttachment "c:\Daily_All_Current_Schedule.xls"

'this sets mail priority.... 0=low 1=normal 2=high

Mail.Priority = 1

'Send the email!

Mail.Send

Main = DTSTaskExecResult_Success

End Function

tygun SSCrazy Points: 2381 More actions · Answer 2

Steve, I went to the site you supplied but there was no code. All I see is the users guide. Are you saying I should look up the filesystemobject?

Thanks for your help.

st

Gerhard Rausch SSC Journeyman Points: 83 More actions · Answer 3

Hi,

0. Excuse my bad english (no native english speaker)

1. Renaming the File

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vaobjfilesystemobject.asp

Look at the methods of the object

2. Sending Email

To send an email I´m using "Email Task" at the end of the workflow of the DTS package. The information about the attachment path, email adress, subject, etc. is stored into "global variables" (using ActiveX Task to set them). After setting the variables, they are mapped by "Dynamic Properties" to the properties of the "Email Task".

The only "problem" is, that you need to install a MAPI Client and you cannot choose an arbitrary email sender.

Best regards

WiWo

esserm Old Hand Points: 340 More actions · Answer 4

Try this one:

declare @outfile varchar(255)

declare @cmdshell varchar(255)

declare @msgsubject varchar(255)

set @outfile = 'd:\path\QryResult_' + convert(varchar(6), getdate(), 12) + '.asc'

set @cmdshell = 'rename d:\path\yourfile.asc ' + @outfile

exec xp_cmdshell @cmdshell

set @msgsubject = 'This is the Query result for ' + convert(varchar(10), Getdate() , 102)

exec xp_sendmail @recipients = 'a@company.com; b@company.com',

@copy_recipients = 'c@company.com',

@query = 'select GetDate()',

@subject = @msgsubject,

@attachments = @outfile,

@attach_results = 'false'

Matthias

Gerhard Rausch SSC Journeyman Points: 83 More actions · Answer 5

Hello,

Matthias solution works also fine.

I´ve implemented it once similar, but had some problems with xp_cmdshell and file access to Novell Server and running the packeges sheduled.

So I decided to use ActiveX in DTS to avoid those problems.

GR

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Was bedeutet WiWo?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]