Loading filenames into table during file load (SQL Server)

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.