none
move multiple files from multiple folders, maintain directory structure, delete originals

    Question

  • I think SSIS can accomplish what I need to do but I don't know enough about it to be certain.  I have an Access database containing approximately 1,500 files in various directories on multiple servers identified by UNC path. I need to copy these files to a new server while maintaining the directory structure. I do not need to copy the entire directory, rather only the few files in each folder that are flagged in the database.

    For example:

    FROM \\server1\department\user\folder\flagged file TO
    \\server3\department\user\folder\flagged file
    FROM \\server2\department\group\project\folder\flagged file TO \\server3\department\group\project\folder\flagged file

    Once I verify that the copy was successful, I need SSIS to delete the file from its original location, again using the data in Access.

    Can SSIS perform these tasks, and does anyone know of any resources to point me in the right direction?

    Thanks,
    Chris
    Thursday, June 12, 2008 5:05 AM

Answers

  • SSIS can accomplish your task, but is it the best solution for the task you outlined here?  You could just as easily write a VB or C# app to do the exact same thing because you're not really transforming any data.  You'd be asking Access for some data, and then performing a lot of file system tasks, copy file, if successful then delete from source folder, etc.

     

    In SSIS, you can query the Access database, populate the information to a variable, use a For Each Loop Container to pass the UNC and File name to a File System Task to move the file, and then add an on success constraint to another File System Task that would delete the source file.

     

    When I get to work, I'll look for the link I've seen here about an introduction to SSIS, or maybe someone else will provide it, before I get there.

     

    Edit: here's the link, and I also found some good code examples and walk throughs here.

    Thursday, June 12, 2008 3:53 PM

All replies

  • SSIS can accomplish your task, but is it the best solution for the task you outlined here?  You could just as easily write a VB or C# app to do the exact same thing because you're not really transforming any data.  You'd be asking Access for some data, and then performing a lot of file system tasks, copy file, if successful then delete from source folder, etc.

     

    In SSIS, you can query the Access database, populate the information to a variable, use a For Each Loop Container to pass the UNC and File name to a File System Task to move the file, and then add an on success constraint to another File System Task that would delete the source file.

     

    When I get to work, I'll look for the link I've seen here about an introduction to SSIS, or maybe someone else will provide it, before I get there.

     

    Edit: here's the link, and I also found some good code examples and walk throughs here.

    Thursday, June 12, 2008 3:53 PM
  • Thanks, Chris -- I'll check out the links and go from there.

    Chris
    Monday, June 16, 2008 3:00 PM