Jeff Moden (6/25/2013)
I'm using a Schema.ini file because when I load these files without one, the data becomes garbled.
"Load these files" how and where? I'm asking because one of the biggest problems you're having seems to be with the creation of the Schema.Ini file for each file. If you're using something like BULK INSERT, the Schema.Ini file isn't necessary. You can just change to a "raw" type file setting.
Also, you didn't answer my question. Why can't we move "all" the files at once and send a single email with the list of files moved?
Sorry, when I referred to "these" files, I meant the test files they've been sending. There will only be one file to move at a time when their process is finalized. So one file, every Monday (morning hopefully).
I tried with bulk insert, and the data did not pipe out to columns correctly. I'm using a Schema.ini because it was more straight forward to create for me than a .fmt file.
The files they're sending are being created overseas, so I haven't gotten a straight answer about how they're being created. They're supposed to arrive to me Unicode, tab-delimited, but there's something weird about them, and the extension is just being changed to .txt from whatever it is originally. It took three weeks for them to stop sending me UTF-8 files (I know, code page 65001).
So, I ended up using openrowset with a Schema.ini that runs essentially like this, but within a SP:
select * into sample.dbo.[eriktest]
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Text;Database=\\server\Sample\;
FORMATFILE=\\server\Sample\Schema.ini',
'SELECT * FROM [file.txt]')
Eventually it will be going to a real table when they've finalized the layout.