question

I would use BPC, but I can't offhand thing of a way to get the Filename into an additional column in the imported data.

Perhaps:

BCP the data into a staging table Run a SQL command - e.g. using OSQL - to transfer the data from Staging table to Real table, and pass that the name of the original file.

BATCH file should be able to do this with

FOR %%f IN (*.log) DO BCP ... etc

If you have the SQL 2005 or above client tools then you could do this in an Integration Services Package. Assuming that all of your text files have the same schema, fire up BIDS and do the following:

• Add a column to the table to hold the file name
• add a flat file and oledb data source
• create a variable to hold the file name, scoped at the package level
• create a data flow
• add a fat file source and oledb destination
• use the derived column to add the file name variable (or use expression to change it) to the data flow pipeline
• map the columns in the data flow
• create a for each loop in the package, set it to iterate through your directory and assign the file name to your variable
• add the data flow to the for each container
• use an expression on the connection string property of the flat file data source so the connection string is changed every time the file name variable changes

Run the package and it should loop through the file in the directory and enter them into the table with the file name in your new column.