Detect new files and populate date

Hi, I need to create a SSIS package for following usage:

I have a folder called c:\test. Every month, I copy a file with following format testMMYY.txt. (Month Year) from a client (A) for vendor B. I have a sql table which contains filemonth(, clientname and vendorname. How can I create a SSIS package to detect any new files, if any, and populate the month and year into file month, and populate clientname and vendorname.

Thanks,

You can use the FileWatcher task (available on SQLIS.com) to monitor for a file. If it is only a once a month thing, you could just run the package and use the File System task to check for existence of the file. You'll need to use a expression to set the ConnectionString property of your FlatFile connection manager to the correct value by concatenating the proper month and year into the file name.|||

Thanks for the reply.

Maybe my description is a little bit misleading, my issue is to identify latest file and transform the file name, e.g. I0407.txt into April 2007 and insert it into the filemonth field in the vendorfile table.

Any thoughts?

Thanks,

|||You can use a script task and the DateAndTime class's MonthName function to translate the file name. This post (http://forums.microsoft.com/msdn/showpost.aspx?postid=1467831&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1) has the script for sorting the list of files in a directory and outputting in a DataTable object. Just take the last row of the data table for the latest file.