Loading filenames into table during file load (SQL Server)

Modified on Mon, 27 Nov 2017 at 08:36 AM


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


      SET LOAD_TABLE=load_budget_data


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


      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%



      REM goto LABEL LOAD


      goto LABEL LOAD

15. Save the script.

16. Run the load table.

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select atleast one of the reasons

Feedback sent

We appreciate your effort and will try to fix the article