How to loop through dynamic txt files in SSIS?

Question

I have a folder that automatically receives different .txt files on a daily basis. There are many different .txt files with different names, but the files I'm interested in loading are the same format, extension, and named the same, with the exception that the date is added to the end of the file name. For example, each file is named TextFile_20230829.txt, TextFile_20230830.txt, TextFile_20230831.txt... etc.

I'm looking to load the .txt file to a table in SQL Server on a weekly basis and would like to load the files for each day that week.

For example: I would run the SSIS package on Friday, 9/1, and it would loop through the folder and load each file (TextFile_20230828.txt, TextFile_20230829.txt...TexFile_20230901.txt), but would not load the other files, such as OtherTextFile_20230829.txt.

I've looked at similar questions on here and know I'll need a Foreach Loop Container, but I can't find anything that specifically handles dynamic file names.

Does this answer your question? [SSIS - How to loop through files in folder and get path+file names and finally execute stored Procedure with parameter as Path + Filename](https://stackoverflow.com/questions/38151342/ssis-how-to-loop-through-files-in-folder-and-get-pathfile-names-and-finally-e) — Thom A, Sep 01 '23 at 08:20
Does this answer your question? [How to import text files with the same name and schema but different directories into database?](https://stackoverflow.com/questions/19957451/how-to-import-text-files-with-the-same-name-and-schema-but-different-directories) — Thom A, Sep 01 '23 at 08:21

score 0 · Answer 1 · answered Sep 01 '23 at 04:52

The same can be achieved using native T-SQL as follows:

declare 
     @week_date_start date = GETDATE() /* adjust to week start date */
    ,@week_date_end date 
    ,@path_name varchar(256)='C:\'
    ,@full_name varchar(50)
;

set @week_date_end = DATEADD(day, +7, @week_date_start);
while @week_date_start < @week_date_end begin
    set @full_name = @path_name + 'TextFile_' + convert(char(8), @week_date_start, 112) + '.txt';
    exec( 'BULK INSERT db.dbo.your_table from ''' + @full_name + ''' WITH (FirstRow=1, ROWTERMINATOR =''\n'')' );
    set @week_date_start = DATEADD(day, +1, @week_date_start);
end

Note: you may need to adjust the BULK options according to your text file format. Also, if your execution date differs from your week start, you may need to calculate it.

How to loop through dynamic txt files in SSIS?

1 Answers1