Using DTS to transfer multiple tables via script..

[Dilyias]

Hi,

I am trying to transfer about 30 or so tables from the AS400 to text files via DTS. I CAN do it by manually adding a text connection for each of the 30 text files, but am wondering if there is a simpler way to do this.

I have gotten the package to loop via scripts, but after the first data copy, it doesn't update the transformations or destination columns based on the new table. (when I update the source sql of the data pump task)

This is currently on a SQL 7 database, although I could move it to a 2K if need be.

Or is DTS not built for this -- and I would be better off writing my own little vb app to perform the transfer.

Any ideas?

Thanks,
Eric

 

[tlbroadbent]

The SQL DTS site is a great resource for DTS questions. Check out the following link.

How can I change the filename for a text file connection?

http://www.sqldts.com/default.aspx?6,101,200,0,1

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Dilyias]

Actually, I have that part working just fine. I can loop through and transfer the same source table to multiple text files with different names (and, I used the looping example from sqldts.com to get me started But when the source table changes (I change the SQL statement to select from a new table with all different columns) it gives me an error that column 'xxx' could not be found on the data push.

I'm guessing I have to manually define the columns and transformations, or "Clear" something, but am unsure as how to do it.

 

[digowa]

Did you find a solution in the meantime?

I have a similar situation, where I try to import several text files with different number of columns. If you found any solution, it would be great if you could post your approach. Would help me much ...