connection manager in SSIS

I'm creating an SSIS package in Business Intelligence  Studio which  takes a csv file and loads it into a database table.
The csv file is dumped on a network share on a weekly basis.
I want to schedule a job that automatically runs an SSIS package that loads this file into the database.

The problem is that the name of the file changes on a weekly basis

Is it possible to  create a Connection Manager that can accomodate these file name changes?
Who is Participating?
Alpesh PatelConnect With a Mentor Assistant ConsultantCommented:
Create File Connection runtime using Expression.

"\\\\" +  @[User::FILE_SHARE] + TRIM(@[User::Done]) + LTRIM(@[User::FileName]) + (DT_WSTR,4)YEAR(GETDATE()) + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) +  RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) +   ".txt"
AmmarRConnect With a Mentor Commented:
if all the files are in one directory

you can loop through all files in the directory and copy them to sql using Foreach Loop Container
AmmarRConnect With a Mentor Commented:
here is another useful article

Using SQL Server Configurations to configure Flat File Connection Manager
blossomparkAuthor Commented:
Hi PatelAlpesh  and  AmmarR, thanks for your responses...that gives me plenty to work with...Thanks again :-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.