SQL Server 2000 DTS Package Dynamic Properties query not working

[NickW2]

I am trying to use a Dynamic Properties task for the first time.
I have a DTS Package containing a transform data task which outputs to a file named "\\servername\folder\xxxxxx.csv".
I have set up a dynamic properties task which uses a query
SELECT '\\servername\folder\'+REPLACE(CONVERT(varchar(10),getdate(),3),"/","")+'.csv'
to try to set the above filename (DataSource property) to use today's date.
However, it appears to have no effect, and the results are still output to \\servername\folder\xxxxxx.csv .

I have now also set up the same query for the property DestinationObjectName on the DataPumpTask, but that didn't change anything.

What do I need to do to force the dynamic properties to be used?

 

[jgoodman00]

Dim oConnection As DTS.Connection
'Modify the path to the target file
Set oConnection = oPKG.Connections("Connection 2")
    oConnection.DataSource = strTarget
Set oConnection = Nothing

I use this in a number of packages & it works perfectly...


James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[NickW2]

Oops. It appears that I made a syntax error in the query.
It should have read
<b>SELECT '\\servername\folder\'+REPLACE(CONVERT(varchar(10),getdate(),3),'/','')+'.csv'</b>
using single quotes in the REPLACE function.

It then works.

If I had tried clicking on "Refresh" when setting up the query I would have spotted something was wrong a lot earlier!