I've got a bunch of CSV files (21) which all contain the same fields but are only distinguishable from their file names. I.e. file.001 file.002 etc. I need to collaborate them all into one table on our Datawarehouse. I’m currently doing the following:
1. Drop & recreate a temp table with an additional column as a default value based on the corresponding file extension (manually entered into the DTS routine).
2. Import a single file into a temp table.
3. Transfer the temp table data to the permanent table with “Keep Null Values” ticked.
I’ve only got as far as importing 2 of the files (which works!) but is getting messy already with 10 steps so far!
There must be an easy way as a file is being imported to add a value on each row rather then going through all these processes!