Jim Russell (9/25/2008)
So the plan (unless I trip over unforseen obstacles) is:
1.Build a script/query to generate the Schema.ini file describing all my input file formats in the linked directory.
2. Download all my input .txt files to that same directory.
3. Run one script that, for each input file, truncates the old target and appends the data from the new linked .txt file.
4. Rename (timestamp) the input .txt file and move it to an archive directory.
A followup progress report:
I generated the Schema.ini file describing all my input file formats, and most tested ok. Be warned that any errors in the file/column definitions just result in obscure, non informative error messages (Error: 7311) -- it either works (which means the Schema.ini is good) or it does not (could be anything wrong in the Schema.ini format.) (Note - all my files are fixed field format.)
However, I hit a snag when testing my largest (most columns) input file. All files take a long time to start transferring, but files with many fields (e.g. 200+) take so long that SSMS times out before the Schema.ini is fully processed. I was testing with "Script Table as/SELECT TO/New Query Editor Window". With just 150 columns, it took 4:40 to generate the select statement; with 200 columns it took 10:07, and then reported a timeout.
That surprises me, since tables with more columns can be imported efficiently in Access, and I would have expected the same performance from an ODBC connection to the Jet Engine. (EXCEPT, I JUST REMEMBERED THAT AN IMPORT TO ACCESS DOES HAVE A MAXIMUM COLUMN COUNT LIMIT.)
I would test a direct import, but select * did not work in this context.
Next step, I guess, is to generate a BCP definition rather than a Schema.ini, unless someone can see the error of my ways.