Dynamic Variables in DTS 1

[shadyness]

Hey all,

I have been looking all over for a quick solution to this, and I even bought Sql Server 2000 DTS (Wrox press), trying to figure out how to do this. Basically, I created a DTS package that runs a compiled exe that I programed. The exe crates an excel file with the current date in the file name (Format(Now, "mmddyy")).

So, my question is how can I set a global variable value that will equal this and most importantly how do I place that variable into the mail task so that daily when the package runs the mail task attatched the correct file?

I have tried many things and the mail task doesn't seem to be reading VB Script.

Thanks,

Ian

<== Some people say they are afraid of heights. With me its table widths. ==>

 

[JayKusch]

Here is what I did to overcome just such a hurdle myself.

declare @FileDate        VarChar(10)
declare @Recipients	 VarChar(400)
declare @Message	 VarChar(400)
declare @Copy_Recipients VarChar(400)
declare @Subject	 VarChar(400)
declare @nAttachment     VarChar(400)

SET @FileDate = 
  REPLACE(CONVERT(Char,GetDate(),110),'-','') + '.txt'

SET	@nAttachment =	'C:\MyDir\' + @FileDate

PRINT 'Attachment Dir & File = ' + @nAttachment 

--  SEND Out Email Alert

EXEC	MASTER..xp_sendmail
	@Recipients		= @nRecipients,
	@Message		= @nMessage,
	@Copy_Recipients	= @nCopy_Recipients,
	@Subject		= @nSubject,
	@Attachments 		= @nAttachment

you can try testing this in Query Analyzer to make sure it all looks and works ok before adding it to your DTS Package.

Thanks

J. Kusch