SSIS dynamic flat file connection to load daily file with date-time,minute,second timestamp

I have to load the daily csv file from a network location which has the date time stamp with minute and second when it gets exported from api and saved to a network location. I am trying to make my package dynamic so it does not change when the file name changes every other day. I have tried using an expression in the flat file manager connection properties but that not working either.

My file name looks like following: DS_All_users_with_additional_fields_2018_12_11_10_00.csv

which i have tried to solve my using the following expression but things gets complicated if there is delay in the csv export and the minute and second changes in the file name:

@[User::DataLoadDir]+"DS_All_users_with_additional_fields_"+(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", 0, getdate() ))+"_10_00.csv"

Any suggestions how to solve this problem?

You can use a foreach loop file enumerator and apply a filespec expression of:

DS_All_users_with_additional_fields*.csv

The * servers as a wild card and will pick up all files matching that string. You can work with this in order to make it flexible based off your needs. In this case, the job will scan for all files that are available in a specific folder that matches the above string. This can then be assigned to a variable, which you can use to dynamically set the connection string.

I don't think you can add the * into the connection string itself.

Update

To set a connection manager's connection string property, see the photo below. It is import to note that this solution will change the work flow. Your initial work flow was telling the connection manager what file to specifically look for. However, by implementing a foreach loop, the job is now searching for any and all files that are available in a specific folder path. Note: you will need to make sure that you include the fully qualified domain name (FQDN) in the connection string variable (i.e., \\networkpath\filename.csv)