Hi People,

I have a DTS package that dynamically loops through a specified list of
files. Importing each file into the appropriate database table using a
Data Driven Query to perform an insert or update. So for each file I
have the appropriate source file name, destination table, insert query,
insert columns, update query and update columns.

When the loop starts I specify the above values to the Data Driven
Query, drop any transformation and recreate the transformation. It
imports the first file fine. Then the next file is retrieved, all the
values for the new file is specified to the Data Driven Query, drop any
transformation and recreate the transformation. When the file attempts
to import the previous files data is being used to the Data Driven
Query. So it attempt to add the wrong file's column 2 into the wrong
tables column 2.

Here is a quick example:
[authors]
SourceFileName=authors.csv
DestinationObjectName=[pubs].[dbo].[authors]
InsertQuery=INSERT INTO authors (au_id, au_lname, au_fname, phone,
address, city, state, zip, contract, mod_type, mod_datetime,
mod_location) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
InsertColumns=au_id,au_lname,au_fname,phone,addres s,city,state,zip,contract,mod_type,mod_datetime,mo d_location
UpdateQuery=UPDATE authors SET au_id = ?, au_lname = ?, au_fname =
?, phone = ?, address = ?, city = ?, state = ?, zip = ?, contract = ?,
mod_type = ?, mod_datetime = ?, mod_location = ? WHERE (au_id = ?)
UpdateColumns=au_id,au_lname,au_fname,phone,addres s,city,state,zip,contract,mod_type,mod_datetime,mo d_location,au_id

[titleauthor]
SourceFileName=titleauthor.csv
DestinationObjectName=[pubs].[dbo].[titleauthor]
InsertQuery=INSERT INTO titleauthor
(au_id,title_id,au_ord,royaltyper,mod_type,mod_dat etime,mod_location)
VALUES (?, ?, ?, ?, ?, ?, ?)
InsertColumns=au_id,title_id,au_ord,royaltyper,mod _type,mod_datetime,mod_location
UpdateQuery=UPDATE titleauthor SET au_id = ?, title_id = ?, au_ord = ?,
royaltyper = ?, mod_type = ?, mod_datetime = ?, mod_location = ? WHERE
(au_id = ?) AND (title_id = ?)
UpdateColumns=au_id,title_id,au_ord,royaltyper,mod _type,mod_datetime,mod_location,au_id,title_id

In the above example authors are imported fine for every occurrence of
the authors file yet the titleauthor are attempting to import the
authors.csv file again not the titleauthor.csv.

I am setting the source connection and SourceObjectName of the Data
Driven Query Task.

Could anybody please help.