CODEWITHSUNDEEP
sql-serverssis
Poppy
Poppy

Reputation: 3092

Dynamic flat file connection string in SSIS

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.

Upvotes: 2

Views: 4605

Answers (2)

LCJ
LCJ

Reputation: 22661

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

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31795

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.

Upvotes: 3

Related Questions