SSIS - Move List of Files from One Folder to Another

1
votes

I have a folder SourceFolder with about 10,000 PDF documents.

I have a list (FileList.csv) with the names of about 1,000 of those files.

I want to move the files on that list from the folder they are in to an empty folder I have created (DestFolder). I am using SSIS 2013.

As a Proof-of-Concept, I successfully configured a ForEachLoop container using a ForEachItem Enumerator with a FileSystem Task inside, and moved 2 of the files.

However, I had to enter the filenames by hand in the ForEachLoop Editor --> Collection --> Enumerator Configuration window.

I have Variables configured for FileName, SourceFolder, and FullSourcePath, and everything works.

My question is - How can I connect to the flat file to get the filenames into the variable?

I'm not allowed to post images, so I will try to explain what I have tried.

  1. adding a flat file source and Connection manager and using the Expressions in CM Propoerties to assign the FileName variable to Connection String (no luck)

  2. Feeding the FlatFile Source into a RecordSet Destination and assigning the result set to an ObjectVariable, ANd changing the ForEach Loop Container configuration to ForEach ADO Enumerator, with ObjectVariable assigned to ADO Source Object variable (no luck).

This seems like such a simple task, I hope I am missing something obvious. Aopologies for not including images.

3
ssis
#2 should work fine. Can you provide more details on what happens, maybe the SSIS log? – Joe C
The error is "Error: Variable "User::ObjectVariable" does not contain a valid data object". Suggests to me that the data is not making it from the Flat File Source to the Recordset Destination. I placed a data viewer in between them - it shows nothing. I can see columns from the Flat File Source Editor and Columns and data from the connection manager Editor. – MCurry

3 Answers

0
votes
  1. Create a variable of type Object to store your file names.
  2. In a script task populates a String Array with the file names and assign it to the object.
  3. Create a precedence constraint between your script task and your foreach container.
  4. In the collection tab of your foreach loop container select Foreach From Variable Enumerator as enumerator type.
  5. In the variable mapping tab select the variable that will store the current file name during the enumeration.
0
votes

I finally got this to work and thought I would post my solution for anyone else who might face the same issue.

I used an SQL Task with an embedded query to get my list of filenames straight from the database.

I fed this into a ForEachLoop Container with a File System Task inside.

I created the following variables:

  • SourceFolder | String | B:\Desktop\Source\
  • DestFolder | String | B:\Desktop\Dest\
  • FileName | String |
  • FileList | Object |
  • FileFullPath |String | (Expr:) @[User::SourceFolder]+@[UserFileName]

Connection Managers

  • OLE DB for the database (obviously)
  • DestFolder | UsageType: | Existing Folder | Browse to the Folder
  • SourceFolder | Usage Type: | Existing File | Point to ANY file in the Folder
  • IMPORTANT: In the SourceFolder Connection Manager, set the following Expression:
  • ConnectionString | @[User::FileFullPath]

In the SQL Task Editor | General window:

  1. Set the ResultSet to Full result set
  2. Paste the query into SQL Statement

In the SQL Task Editor | ResultSet window:

  1. Result Name = 0
  2. Variable Name = User::FileList

In the ForEach Loop Editor | Collection window:

  1. Enumerator = ForeachADO Enumerator
  2. ADO object source variable = User::FileList
  3. Enumeration Mode = Rows in the first table

In the ForEach Loop Editor | Variable Mappings window:

  1. Variable = User::Filename
  2. Index = 0

In the ForEach Loop Editor Properties:

  1. Set the Maximum Error Count to some number greater than the number of files you are looping over. (This covers the situation where your query generates a filename that is not in your Source Folder. I'm sure there's a more elegant way to do this, and I look forward to learning it someday!)

In the File System Task Editor | General window:

  1. IsDestinationPathVariable = False
  2. DestinationConnection = DestFolder
  3. Operation = MoveFile
  4. IsSourcePathVariable = False
  5. SourceConnection = SourceFolder

In the File System Task Properties window:

  1. Set the Maximum Error Count > the number of files in SourceFolder

The package will now run and generate an error for every filename in the FileList that does not have a matching file in the Source Folder, but it will run and move the files.

Gotchas:

  1. Don't forget the "\" at the end of the filepath variables
  2. Don't forget to set the Expression in the Source Connection Manager Properties window (see above).

Thanks to everyone for your help.

0
votes

Much quicker way to accomplish this!

drop the foreach loop task down. under collection select Foreach File Enumerator. folder is where the files are. c:\SrcFiles leave fully qualified and put in .txt or whatever file extension is under variable mappings add variable named FileName string type and make sure it's in scope of package

drop file system task into container Source Variable is FileName Destination Connection is location you are moving to. d:\ReceivedFiles Overwrite destination = True DelayValidation = True

Voila!