Loading filenames into table during file load (SQL Server)

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

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.

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