CODEWITHSUNDEEP
regexssisconnection-string
John Michailidis
John Michailidis

Reputation: 1

Loop over all the files from a folder and import one that match regex criteria in ssis

I have a particular task that i need to get done but i find difficult to find any matching case on the internet.

In the company i work for, we have a VPN that every day folders named with the current date are dropped.

I need to create an ETL (in SSIS) which will loop over all the files from specific folders and extract one file that i need and then populate a table.

The name of the particular file changes every day. It holds the same n first characters and ends with a date that may be one day before the current date or two or three.

It's straightforward that i need to use a foreach loop container to loop over all the files of the folder. But how can i select the one file that starts with specific characters?

Essentially, does anybody know how can I use regular expression in a connection in SSIS?

Thank you,

Upvotes: 0

Views: 767

Answers (2)

Chris Mack
Chris Mack

Reputation: 5208

You could use a Script Task for this, and use the System.IO namespace to find the file you need. You could use System.IO.Directory.EnumerateFiles to loop over the files, or, as you know what the filename will look like, you could check for the existence of the file with yesterday's date, then the day before, etc. in a loop (going back as far as you wish to), and then set a variable with the file path once you find one that exists. You could then use this variable as the Connection Manager's Connection String, setting it in the Connection Manager's Properties (Expressions).

Upvotes: 0

Prabhat G
Prabhat G

Reputation: 3029

SSIS for each loop container accepts * operator. So for my case, my files are a1,a2,a3,a4 etc. a* then read them sequentially. You just need to capture it in a variable and use it inside loop.

Try like this below :

new

Upvotes: 0

Related Questions