Approach:


1. Drag and drop table as you'd expect, to create a script load table


2. Change the file name to include a wildcard, eg: *.txt


3. Add an archive folder value


4. Add an archive file value, assuming you want to timestamp files, out this in: %SHORT_NAME%.$YYYY$$HH$$MM$$HH$$MI$$SS$


5. Create the table and a script


6. Add the file_name column as the last column of the load table with varchar(1000) as its data type


7. Recreate the load table- DO NOT regenerate the script


8. Rename the table to have _tab on the end of its name


9. Browse the DW connection


10. Drag and drop the load table to define a view called the original name of the load table (ie: without the _tab on the end)


11. Remove the file name column from the view


12. Create the view


13. Edit the script


14. Changes as follows:

      

      if defined WSL_LOADTABLE (SET LOAD_TABLE=%WSL_LOAD_TABLE%) else SET LOAD_TABLE=load_budget_data

      to:

      SET LOAD_TABLE=load_budget_data

      

      echo exit (SELECT @@ERROR) >> %FILECTL%

      to:

      echo GO >> %FILECTL%

      echo UPDATE %LOAD_SCHEMA%%LOAD_TABLE%_tab SET file_name = '%FILE_NAME%' WHERE file_name IS NULL >> %FILECTL%

      echo GO >> %FILECTL%

      echo exit (SELECT @@ERROR) >> %FILECTL%

      FOR %%A IN (%FILE_NAME%) DO SET SHORT_NAME=%%~nxA

      

      REM goto LABEL LOAD

      to:

      goto LABEL LOAD


15. Save the script.


16. Run the load table.