DTS - Datapump, Transform Data

Hi,

On my MS SQL Server 2000, I am trying to create a generic way to load tables into my datawarehouse.

I have as input to the process a large number of table definition(s) stored individually as files on my server. And, ascii delimited data files in various locations but mostly accessible via NFS mounts.

I created two DTS package in MSSQL2K that in theory represents what I want to do:

package1
... invoke package2 with global variables to load a system of related tables

package2
... check for a trigger file
... set the "Execute SQL Task" statement to my first file
... run the "Execute SQL Task" which drop/add's a table
... set a "Connection" to a data source file that I want to use
... run the transformation
and, with that my package starts to fall apart
... set the "Execute SQL Task" statement to the next file, and
...... goback and execute it

I can't figure out how to set the table in the transformation section to the table I want to use. And, I assume next to have the transformations links between the source and new table relinked.

The source files contain in the first row the column names as found in the tables I just created.

thanks,

Dave Rowsome