I need to get data from multiple text files based on the name of the text file, into a table in SQL Server 2000. I have written a DTL package that can get data from one text file where the text files name is hard coded as the Source. The text file names are as follows: Log_20090923.txt, Log_20090924.txt - one for every day of the week. I need to get at least 30 days of the text files into the table. How can I do this? Can you give me an example please?
Thanks a lot!
It might be easier to do this via T/SQL. You can use xp_dirtree to find the names of the files, then use the BULK IMPORT command to import each file into SQL Server.
SSIS is another alternative.
mrdenny, What if I want only certain files, not all files?
Makes perfect sence!! Thanks alot. I can't wait to try it on Monday.
If this is a one-off you could concatenate the files together, and then import the single filename.
From a command line:
COPY MyFile1+MyFile2 MyOutputFile
If it needs to be a scheduled task you could still do similar, using a BATch file, and have the BATch file call BCP to push the text file(s) into a table in the DB.
