Dynamic flat file connection string in SSIS

Question

I'm using a flat file connection source in my SSIS Package. The filename has the following naming convention viz.,<<filename><year><month><date><time>>. Example - sample201703291515.txt. The filename would have different time appended to it as the file creation time would differ. I have created expressions in the connection string to fetch the name for the current date.

"c:\source\sample"+(DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() ))+DT_STR,4,1252)MONTH( DATEADD( "dd", -1, getdate() ))+(DT_STR,4,1252)DAY( DATEADD( "dd", -1, getdate() ))+".txt"

But since the time changes, is there any possibility to get the filename dynamically or is there any expression I can add up? Please help.

Tab Alleman · Accepted Answer · 2017-03-29 16:26:15Z

3

The normal way to handle this is to use a foreach loop that goes through all the files in the folder. Then use a script task to look at the file name and compare it to some known pattern. So if you know you want "sample20170329xxxx.txt" where the xxxx is some unknown time string, you can either use a Regex in the script task, or you can just compare a substring of the file name and only look at the characters before the time portion of the name, to see if it's the file you want.

Then, if it is the file you want, you set your connection string to use that filename, otherwise, loop to the next file.

answered Mar 29, 2017 at 16:26

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

LCJ · Accepted Answer · 2021-07-07 17:53:43Z

0

Store all filenames in an object variable. Create a Foreach loop container. In each loop iteration assign current file name in a string variable. In the conectin manager, right click and see properties. In the properties select "Expressions". Then seelct the ellipse which will open a new window. Select ConnectionString as property and click ellipse. Now drag drop the required variable to make it as dynamic.

Also refer Dynamic Flat File Connections in SQL Server Integration Services

answered Jul 7, 2021 at 17:53

LCJ

22.8k69 gold badges269 silver badges439 bronze badges

Collectives™ on Stack Overflow

Dynamic flat file connection string in SSIS

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related