none
Moving Folders Based on a List

    Question

  • I have the need to move a certain set of folders from one directory to another. I've found several posts that explain how to move files from one directory to another, but those didn't quite provide what I needed.

    Essentially I have a text file that lists 700+ folder names. I need to iterate through those and starting at a certain "root" directory (E:\Folders) move those subfolders in E:\Folders to another root directory (F:\OtherDirectory). Here's what I have so far:

    I hae a data flow task which is just a flat file connection which contains the list of folders. I then dropped a "For Each Loop" container onto the Control Flow. I then connect the data flow task to the For Each Loop Container.

    I created a variable that has a scope of Packge with a datatype of String. The name is User::Folder.

    Within the For Each Loop Editor, I've done the following:

    In Collection, I chose "ForEach Item Enumerator" as the enumerator. For expression I chose the variable (User::Folder).

    In Variable Mappings, I chose User::Folder as the variable with an index of 0.

    Within the For Each Loop Container I drop a File System Task. Here are the settings for that:

    IsDestinationPAthVariable = FALSE

    DestinationConnection = connection to the new "root" directory

    Operation = CopyDirectory

    IsSourcePathVariable = FALSE

    SourceConnection = connection to the original "root" directory

    Am I heading down the right path here???

    Any help would be great! Thanks!!!

    Here's where I get stuck!! Should that data flow task be inside or outside of the For Each Loop?


    A. M. Robinson
    Thursday, September 16, 2010 8:59 PM

Answers

  • Not too far off - but Sudeep is not quite right either.

    Keep your Data Flow Task outside the loop.  In that Data Flow Task, read your file, and route the flow into a Recordset Destination, to populate a "FolderList" SSIS variable (of type Object).

    In the Foreach, use an ADO enumerator, and map the 0 index to your Folder variable (like you described).

    Create a new string variable, called "SourceFolder", and set its EvaluateAsExpression to true.  Set the expression to:

    "E:\\Folders\\" + @[User::Folder]

    Change your file system task's IsSourcePathVariable to true, and set the SourcePathVariable to SourceFolder.  My only doubts with this is whether the File System Task will move folders, not just files.


    Todd McDermid's Blog Talk to me now on
    Thursday, September 16, 2010 9:38 PM
    Moderator
  • Todd:

    You were right on...! Thank you. Also, thanks to everyone else who contributed. One thing you had asked about was whether Copy Directory would copy just the files or the directory/folder names themselves and their contents. Well, unfortunately Copy Dierctory only copied the contents, which in my case is fine. I may play around with things later to see how I can get the whole copy folder names as well to work - if that's even possible!

    Now the next part is to once I move those folders, I'm going to be doing essentially a mass DROP of the databases. Not something I relish but I do what I'm told!

    I've exxentially just reused the same variables and instead of having a File System Task in my For Loop, I put in an Execute SQL Task. I created a new variable called DropString and set EvaluateAsAnExpression to "True" and set it equal to "DROP DATABASE" + @User::Folder. I then set the SQLSourceType in the Execute SQL Task to a Variable and set that = @DropString.

    We'll see how it goes...!

    Thanks again everyone...

     


    A. M. Robinson
    Friday, September 17, 2010 5:10 PM

All replies

  • What u are doing incorrect is the variable mapping

    You need to use another variable to do variable mapping like FileName which will store the complete filename to be used in the file task as the source.

    In the file task destinatin you need to have a destination folder and file name else all the file will be copied one over the other if you set overwrite.

    For reference on foreach loop settings check this. You would need to use file enumerator and not ado as I have used.


    Sudeep's Domain   Tweet me..
    Thursday, September 16, 2010 9:32 PM
  • Not too far off - but Sudeep is not quite right either.

    Keep your Data Flow Task outside the loop.  In that Data Flow Task, read your file, and route the flow into a Recordset Destination, to populate a "FolderList" SSIS variable (of type Object).

    In the Foreach, use an ADO enumerator, and map the 0 index to your Folder variable (like you described).

    Create a new string variable, called "SourceFolder", and set its EvaluateAsExpression to true.  Set the expression to:

    "E:\\Folders\\" + @[User::Folder]

    Change your file system task's IsSourcePathVariable to true, and set the SourcePathVariable to SourceFolder.  My only doubts with this is whether the File System Task will move folders, not just files.


    Todd McDermid's Blog Talk to me now on
    Thursday, September 16, 2010 9:38 PM
    Moderator
  • Thanks for pointing that out. I missed the part where he mentioned " I have a text file that lists 700+ folder names". My bad .....

    Check this post of mine it deals with the same scenario.


    Sudeep's Domain   Tweet me..
    Thursday, September 16, 2010 9:53 PM
  • I'm not rtying to move files...I'm moving folders...
    A. M. Robinson
    Friday, September 17, 2010 2:20 AM
  • The For loop is the right object. The File system Task is the right object. The data flow task need to be outside the for each. 

    So you approach is right. 

    Within the For loop can you put a script task and have a messagebox to display the folder variable value. I just want to make sure that the enumerator object for the for loop is set properly. If not there would be some problem in the Data flow Task.

    Also can you check whether Move Directory need to be used or the Copy Directory. Can you check whether Copy directory moves all the files from one directory to another directory and not exactly move the directory

    Let know.


    vinu
    Friday, September 17, 2010 4:37 AM
  • Tried this part "Copy directory" operation , copies all the sub folder and the files in it. But if the file or directory already exists in the destination , it throw error. May be you should handle it if required.
    Friday, September 17, 2010 6:17 AM
  • Todd:

    You were right on...! Thank you. Also, thanks to everyone else who contributed. One thing you had asked about was whether Copy Directory would copy just the files or the directory/folder names themselves and their contents. Well, unfortunately Copy Dierctory only copied the contents, which in my case is fine. I may play around with things later to see how I can get the whole copy folder names as well to work - if that's even possible!

    Now the next part is to once I move those folders, I'm going to be doing essentially a mass DROP of the databases. Not something I relish but I do what I'm told!

    I've exxentially just reused the same variables and instead of having a File System Task in my For Loop, I put in an Execute SQL Task. I created a new variable called DropString and set EvaluateAsAnExpression to "True" and set it equal to "DROP DATABASE" + @User::Folder. I then set the SQLSourceType in the Execute SQL Task to a Variable and set that = @DropString.

    We'll see how it goes...!

    Thanks again everyone...

     


    A. M. Robinson
    Friday, September 17, 2010 5:10 PM