Move Multiple Files Based on File Names Listed in a Spreadsheet / File

Question

I need to move specific files from a server to another server on a monthly basis.  There are hundreds of files that are in the source directory and I need to move approximately 40 of those to the destination server.  I would like to easily add or delete the file list as needed.  I have seen where several variables were created for for each file name (and one for the path) and the ForEach Loop would go through them.  With 40 or more I was thinking that I could make a connection to an Excel spreadsheet or text file with a record for each file name and read in and and move to the next record and make that value become the content of a "FileName" variable.  Then if I wanted to add another file name I could just add another record to spreadsheet/text file or remove and the package would handle automatically....I know I'd need to do some "file exists" checking but wanted to know if this was a decent approach or if that wheel has already been created and could share with me or direct me to the solution.

Thanks for any help,

Eric

Answer 1

How to Loop through multiple files and move them in different folders?

http://www.singhvikash.in/2013/05/ssis-how-to-loop-through-multiple-files.html

---

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

Answer 2

SSIS is able to read an Excel file.

What you may be best off doing (at least springing to mind in the thought I've put in...), create a DFT task that reads your Excel file and outputs to an Object variable. You will then have your full list of files (and directories if need be) within a variable to be the source of a For Each loop.

Conventional approach would be to only put the files to be processed in the source folder of your package so if the file is in the folder, it is to be processed...

Answer 3

Thanks rguy and RyanAB.  The first solution I believe moves all the files in the directory based on the file type (extension), not just a subset of all the files in the directory.  

Ryan, I've started with your recommendation.  Can you let me know if I'm on track? (relatively new to SSIS)  I created a DFT and Excel Source and outputted to a Recordset Destination.  That feeds a Foreach Loop that I have as an ADO Enumerator.  In the Collection I have the object variable (objFileList) and in the Variable Mappings I have a string variable strSourceFile that will hold the file name as it goes through the records in the dataset. So I assume that the Foreach Loop loads the object variable with the file names in the list.  

Making a big assumption that I have this correct, I am lost on how to get the file names that were loaded in the object out to I am assuming a File System Task.  How do I unload the contents of the object to the FST?  Use the file name (strSourceFile variable) for the FST while it's loading the object? Add another Foreach Loop with the FST inside that?  

thanks for your help and patience with my inexperience,

Eric

Answer 4

Whoops, I guess that link doesn't work.  Try this.

http://stackoverflow.com/questions/15287943/how-to-move-files-to-different-folders-based-on-matching-filename-and-folderna

ALSO

http://help.pragmaticworks.com/dtsxchange/scr/FAQ%20-%20How%20to%20loop%20through%20files%20in%20a%20specified%20folder,%20load%20one%20by%20one%20and%20move%20to%20archive%20folder%20using%20SSIS.htm

Does that help?

---

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

Answer 5

First bit all sounds good. For reading the files, you need to define your Connection Manager with the columns of the file, define an expression against this Connection Manager to set the source to the variable you are using for the filename (strSourceFile). Then, inside the For Each, add a DFT and a File Source, inside the DFT, that is set to your File Connection Manager, process as needed.

If the files have different definitions, you will need to define multiple Connection Managers and multiple DFTs, then use logic (via expressions) to determine which DFT should run on receipt of a particular file

Answer 6

I looked at the examples from the links and I apologize if I did fully understand how they worked but it seems like it was reading in the files that were to be moved.  The only file that I was thinking I'd need to read in is the Excel spreadsheet that has the file names listed in column A.  That would provide the dynamic list of files (txt, doc, pdf's, etc) and the source and destination path's would remain static.

1. Read Excel File to Recordset - one field with file names

2. Load object variable with recordset values using Foreach Loop

3. ?? Another Foreach Loop to feed FST to move files using path source and destination variables (constant) and file name variables from the object contents that contains the 40 or so file names.

Am I off track?  I assume the FST would be located in the second Foreach container?

thanks,

Eric

Answer 7

Hummm, I don't even know SSIS that well; I've only used it a few times in the past.  I know VBA for Excel much better.  I know this is different from your original post, and I have no idea if Excel is even installed on your server machines, but if you do have access to Excel, maybe you can just use a VBA solution.

This is a great resource.

http://www.rondebruin.nl/win/s3/win026.htm

Check this out too.

http://www.vbaexpress.com/forum/showthread.php?19155-Solved-Moving-Excel-files-into-folder-based-on-filename

Finally.

http://www.ozgrid.com/forum/showthread.php?t=182313

---

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

Answer 8

Here are a couple more resources to consider.

https://gist.github.com/shabdar/4582250

http://stackoverflow.com/questions/19383105/move-files-after-changing-theirs-names-how

http://www.ozgrid.com/forum/showthread.php?t=182313

---

Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

Answer 9

Sorry, if you just want to move the files, then use the File Task instead of DFT. But as has been suggested in other posts, a script within Excel could do it for you

Answer 10

I was using the DFT to read in the list of file names into a dataset and the FT to move them.  I was trying to do everything in SSIS.... move the files, then process them...

thanks,

Eric