DTS job to email 'latest' file

[sedgely]

I have a DTS job to export data to excel, then Excel runs some code to generate a new sheet with a combination of a string and the date as the filename (eg SSP_SUMM_31_10_2004.xls). I then need DTS to take the latest file and email this to a group of people.
How do i identify the latest file and tell the package which file to send?

Cheers, Craig
Si fractum non sit, noli id reficere

 

[JayKusch]

If your Excel wsheet is generating the filename based on the prestring of 'SSP_SUMM_' followed by the date in a DD_MM_YYYY format, you could, before the file is email in your package, create a variable that would represent the file needing to be sent ... somthing like:

DECLARE @NewFile VarChar(40)

SET @NewFile = 'SSP_SUMM_' +
RTRIM(LTRIM(CONVERT(Char,DatePart(dd,GetDate())))) +
'_' + RTRIM(LTRIM(CONVERT(Char,DatePart(mm,GetDate())))) +
'_' + RTRIM(LTRIM(CONVERT(Char,DatePart(yy,GetDate())))) +
'.xls'

PRINT @NewFile

Then use the @NewFile to point to as the attachment in your sendmail command.

Thanks

J. Kusch

 

[sedgely]

JayKusch
Thanks for that, as i am fairly new to DTS can you tell me where i would put this code. Sorry for being dim.

Cheers, Craig
Si fractum non sit, noli id reficere