Destination File - Dynamic Name

[whistlerheather]

I have a Dynamic Properties Task in a DTS Package.

I have set the datasource for Connect 2 (the destination txt file) to
Query
Connection 1
SELECT
'\\myserver\my folder\'
+
(SELECT CONVERT(VARCHAR(8), GETDATE(), 5))
+
'.TXT'

When I click refresh, nothing happens. When I click Parse, it says the SQL statement is valid.
When I open qry analyser and run the same query against my sql db (connection 1) it works...

When I run the dts, it doesn't change the file destination name at all.

going.. um.. NUTS.

 

[123FakeSt]

Hmmm I always do this in an activeX script / dynamic properties combination and it works great.

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Option Explicit

Function Main()
Dim strStamp
strStamp = Year(now()) & Right("0" & Month(now()),2) & Right("0" & Day(now()),2)
strOutputPath = "\\MyServer\Scheduled_Reports\Output\"
DTSGlobalVariables("strTimeStamp").Value = strStamp
Main = DTSTaskExecResult_Success
End Function

Then I set my Dynamic Properties to this global variable.

The early bird gets the worm, but the second mouse gets the cheese.

 

[whistlerheather]

fantastic. Thanks so much for your help. its working the active x script/ dynamic properties.
thanks again!