I've been getting my feet wet with DTS, but have come to something I don't know how to do. I have about sixty Excel sheets that are constantly updated, so I need to refresh a table with all of them periodically, because I don't know when they will change.
I have a package that goes out to a directory and reads the excel file names into a sql server table.
What I wanted to do was use that table to feed a global variable in a different package so that I could use it in an Active x script of a transform data task (either right in it or from the properties on an transform data task) as in 'Insert into scrubber select * from gvfilename but it only works for the default values of the gv.
I don't think I can use parameters for the Excel file names, for example in an Exec SQL task, because it looks like DTS, like TSQL, doesn't let you use database objects in variable names, so I created a global variable for the file name in a Dynamic Properties task in addition to another gv for the file type.
The package runs fine, but what I'm missing is how to change the global variable's value when its done importing the contents of the default file name, iow, it works once. Do I need some kind of ADO code in the Active X task to change the value of the gv? Are there any examples of how to do this? iow, my problem may not be so much with DTS as not knowing ADO.
thanks

i figured it out.
what is required is to create two packages, a worker and a controller. the worker keeps a connection to the Excel connection, and the controller loops through a table that provides the Excel workbook name, feeding package global variables that inform the worker of what to do next.
its actually very interesting to coordinate the workings of two dts packages together, but my opinion is that active x, or should i say visual basic, is a rather half witted language. i guess the glory of dts' gui isn't so much that it hides the complexity of tasks, but instead is a way to paper over the limitations of the language; in other words, one should be able to call a second process from within an active x script and return to that point to continue processing. i found the same limitation in error handling in the language. unfortunately the language isn't built that way, necessitating the packages to be designed in a serially rather than nested.
clumsy, but workable.
thanks for your time
drew