Urgent VBScript Query - emailing recordset as xls attachment 1

[panini]

hi there,

I have set up a dts package that mails out a recordset as a csv file attached to an email. My problem is I can't get it to create a new file each time the script runs hence i keep emailing the same file until I delete it and re-run the package - I need to find a way to generate a new file every time it runs.... the code is below (hope it helps anyone trying to do the same thing)

Function Main()

Dim iMsg
Dim iConf
Dim fso

'Create a randome Filename
nRandom = Int((1000000 - 1 + 1) * Rnd + 1000000)
fileExcel = "t" & CStr(nRandom) & ".csv"

'Replace filepath with your virtual directory name or just the slash if it is at the

http://wwwroot.

filePath= "c:\\inetpub\

http://wwwroot\XLS"

filename=filePath & "\" & fileExcel

'Create the File with extension .csv using the FileSytemObject
'If the file does not exist, the TRUE parameter will allow it
'to be created. Make sure the user* impersonated has write
'permissions to the directory where the file is being created.

Set fso = CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.CreateTextFile(filename, True)

'Open the connection and retrieve data from the database
Set cn = CreateObject("ADODB.Connection")

'Connect to the db
cn.Open "dsn=xyz;uid=Group;pwd=xxx"
Set rs = cn.Execute("SELECT orderNo, orderDate FROM orders")

if rs.bof or rs.eof then noOrders = true
if noOrders then
Main = DTSTaskExecResult_Success
End if
if not noOrders then

strLine="" 'Initialize the variable for storing the filednames

For each x in rs.fields
'Separate field names with comma so that these appear in
'different columns in Excel
strLine= strLine & x.name & chr(44)
Next
'Write this string into the file
MyFile.writeline strLine

'Retrieve the values from the database and write into the database
Do while Not rs.EOF
strLine=""
for each x in rs.Fields
strLine= strLine & x.value & chr(44)
next
MyFile.writeline strLine
rs.MoveNext

Loop

'Clean up
MyFile.Close
Set MyFile=Nothing
Set fs=Nothing
Set fileExcel=Nothing

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
Set iMsg.Configuration = iConf

iMsg.To = "xyz@abc.com"
iMsg.From = "xyz@abc.com"
iMsg.Subject = "Order File"
iMsg.AddAttachment filename
iMsg.Send

end if


if err.Number = 0 then
Main = DTSTaskExecResult_Success
else
Main = DTSTaskExecResult_Failure
end if

End Function

 

[Wholsea]

You forgot one key word...

RANDOMIZE

Your random function will continually pull the same number unless you throw that in there before you calc with the RND function.

ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[panini]

ah cool thanks for your help