changing a file connection in DTS 1

[splitthird]

I have a series of xls files I wish to extract data from using DTS in SQL2000. I have worked out how to create a loop and how to get the list of files okay. My problem is that I use a "Dynamic Properties Task" to specify the path and filename for an existing connection using a global variable - that works perfectly when I step through the process in the design window. However when I execute the whole DTS package it does not seem to update the filename (and hence the spreadsheet it is looking at).
This feels like an issue that others may have encountered - can you help?

thanks

 

[jhall01]

Are you executing the DTS as a job? Is this being executed locally or on the Server?

When working in the DTS Designer you are working with directorys locally which if executed on the server won't connect to your local machine.

Another way to update the destination is dynamically change using ActiveX:

DIM pkg, destCon, destFileLoc

set pkg = DTSGlobalVariables.Parent
set destcon = pkg.Connections("destinationname")

destFileLoc = DTSGlobalVariables("gv_name").value

destcon.Datasource = DTSGlobalVariables(destFileLoc)

You can find help with this at

http://www.sqldts.com.

I use this code with all my DTS loops.