12 Mar

SSIS: Moving Groups Of Files

Every example I’ve read for moving files uses the File System Task and a collection of files in a single folder. It’s a super easy move and pretty much all you do is point the File System task at it and roll. But what happens if you have several files, inconsistently named, that have to go to different places? One way to do it would be to create a bunch of File System Tasks but there is a better way.

Lets set up our base file system as follows:

Note I named the files the way I did to simulate my old work environment (we exported data to our web and bi servers). You can name them whatever you like and the naming pattern I use (-web and -server) is just there to see if what I did worked.

Test Data Folder: C:\testData
Server Destination: C:\testData\server
Web Server: C:\testData\web

Now create 6 text (or whatever type you’d like) files in the C:\testData folder. Name them as follows:


What we’re going to do is use SSIS to read through these 6 files and then place copies of them in the appropriate sub-folder.  We’re also not going to use the “web” or “server” part of the names as a crutch.  Treat that part like it doesn’t exist.  This is a several part process that is a bit confusing in some places but it does work and it is definitely better than 6 separate File System tasks.

Step 1: Create a table in a database that lists the source (where they are) and destination (where we want them) path information for the above files.

name VARCHAR(50),
source VARCHAR(255),
destination VARCHAR(255)

Next we populate the table with the relevant information for the files we created earlier.

INSERT INTO fileMoves(name, source, destination)
VALUES ('accounting-server','c:\testData\accounting-server.txt','c:\testData\server'),

SELECT * FROM fileMoves;

id name	              source	                        destination
1  accounting-server  c:\testData\accounting-server.txt	c:\testData\server
2  credit-server      c:\testData\credit-server.txt	c:\testData\server
3  sales-server	      c:\testData\sales-server.txt	c:\testData\server
4  accounting-web     c:\testData\accounting-web.txt	c:\testData\web
5  credit-web	      c:\testData\credit-web.txt	c:\testData\web
6  sales-web	      c:\testData\sales-web.txt	        c:\testData\web

Step 2: Create an Execute SQL Task, process it as a result set and place the result set into an object type variable.

Execute Task - General Tab

Execute Task – General Tab

I have to admit that this threw me for a loop at first. It was not how I expected things to work. Anyways, here come a bunch of configuration steps.

In the General tab.

  1. Drag an Execute SQL task to the control flow.  You can rename it, or not, at your discretion.
  2. Open the result set.
  3. Set the ResultSet property to “full Result Set”.
  4. Set ConnectionType to “OLE DB”.
  5. In Connection, click “New Connection” and point the connection to the database that you created the fileMoves table in.
  6. In SQLSourceType select “Direct Input”.
  7. In SQLStatement type “SELECT name, source, destination FROM   fileMoves”.
Execute Task - Result Set

Execute Task – Result Set

In the “Result Set” tab.

  1. Click Add.
  2. In the “Variable Name” column select “New Variable”.  Use the following properties.
    1. Name: varMoveFileSet.
    2. Value Type: Object.
    3. You can leave the rest in their defaults.
  3. In the “Result Name” column set the value to 0.  If this were a single row result set these would be the ordinal positions but since we’re plowing the the entire result set into the variable we use 0.

Click OK to close this task.

You now have a working Execute SQL task that has passed it’s results into an object type variable with the name of varMoveFileSet.

Step 3: Set up a Foreach Loop Container to loop through the result set.

Foreach Loop - Collection

Foreach Loop – Collection

Now we need to process the result set.  In order to do that we need to use an ADO Enumerator in the Foreach container.  Yeah, I bet you saw that one coming.  Here is how you do that.

  1. Drag a Foreach Loop Container to the control flow and connect the Execute SQL task to it.
  2. Double-click the container to open it.

In the collection tab do the following:

  1. Change the Enumerator value to “Foreach ADO Enumerator”.
  2. Change the “ADO object source variable” to the variable we created earlier “User::varFileMoveSet”.
  3. Set Enumeration Mode to “Rows in the first table”.
Foreach Loop - Variable Mappings

Foreach Loop – Variable Mappings

You have now connected the container to the table results you generated earlier but you still need to map them to variables so SSIS can process them.

Click on variable mappings, then in the variable column create 3 new variables and name them as follows:

  1. User::myName
  2. User::mySource
  3. User::myDestination

Index them as index 0, 1 and 2 in the order they are listed above.

Click OK to close the Foreach Loop container.

Step 4: Add a File System task and configure it to use the information in the prior steps.

This is actually the easiest part of the whole process.  Just do the following:

  1. Drag a File System task into the Foreach Loop container and rename it, or not.
  2. Double-click the container to open it.

ado5Configure the General tab as follows:

  1. Set IsDestinationPathVariable to True.
  2. Set DestinationVariable to “User::myDestination”.
  3. Set OverwriteDestination to True.
  4. Set Operation to Copy.
  5. Set IsSourcePathVariable to True.
  6. Set SourceVariable to “User::mySource”

Click OK to close the File System Task.  At this point you are almost done.  In fact, you might expect to have a working package.  There’s just one small detail, a validation error.

Step 5: Fix the validation error.

Right now you likely have a package with an X in the File System task.  The reason for this is that the package can’t validate the variables.  The way you fix this is by setting the DelayValidation property to true.

  1. Click on the File System Task and click F4 to open the property window.
  2. Find the DelayValidation property and set it to True.
It lives!

It lives!

The red X should be gone and you can run the package.  If you did everything right, or probably more importantly, I wrote it right, you should have the -server files in the server folder, and the -web files in the web folder.

There are other ways to do this and for small groups of files maybe it wouldn’t even be worth it to use SSIS.  I know in the AIX / Linux world I used to inhabit we just used RSYNC, put the commands in a file, and let the crontab run them.  But, if you had a lot of files moving, or you wanted someone to maintain a file list without getting on the server, this could be a good solution.