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
eg
file_11042011.csv
file_18042011.csv

Is it possible to  create a Connection Manager that can accomodate these file name changes?
blossomparkAsked:
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"
0
 
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


http://www.rad.pasfu.com/index.php?/archives/22-Looping-through-specific-files-SSIS.html
0
 
AmmarRConnect With a Mentor Commented:
here is another useful article

Using SQL Server Configurations to configure Flat File Connection Manager

http://www.ssistalk.com/how-tos/using-sql-server-configurations-to-configure-flat-file-connection-manager/
0
 
blossomparkAuthor Commented:
Hi PatelAlpesh  and  AmmarR, thanks for your responses...that gives me plenty to work with...Thanks again :-)
0
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.