locked
Monitor folder and move files RRS feed

  • Question

  • Hi,

    We have a folder where a file will be dropped in (.csv, or .xml) by a user.  When the file lands in this folder, we need to automatically move it to an archive folder (on the same server) AND insert a row into a sql server database.

    This process needs to happen automatically when the file lands in the initial folder.

    Is SSIS the best way to do this (bearing in mind we will be using SSIS 2005)?

    Or should we use some other method?  

    Wednesday, June 19, 2013 4:12 PM

Answers

  • Hi,

    This is a standard requirement for some data warehousing projects and can be done very easily with SSIS 2005 and above.

    The last time I did this we had a package archiving files that was executed via a SQL Agent job every 10 or 20 minutes for a fixed daily time window - for example, from 11am to 6pm. The table holding the file names was used by another SSIS to drive the loading of those files into a data warehouse in the early hours of the morning.

    In the package, you need a For Each task, with a File System task to move the files and an Execute SQL task to write the file names to a database. We used variables and expressions to construct full file paths.

    Kind regards,

    M

    • Proposed as answer by Mike Yin Thursday, June 20, 2013 3:28 AM
    • Marked as answer by chadwell Thursday, June 20, 2013 8:48 AM
    Wednesday, June 19, 2013 4:59 PM