This is a real scenario l had to deal with at work today. I had about thousand folders with multiple files and l wanted to move all these files to a folder on my network where an application will pick up the files and process them into my production database. I started googling for utilities that can help me quickly move these files instead of doing it manually. My google effort didn’t give me any good tool to get this done at the time so I decided to develop a simple SISS package to get the work done. It took less than 10 minutes to develop this package and move all the files to the final folder for processing.
So let me discuss the steps l took in developing this SSIS package and maybe it might help someone out there. I assume you already know basic SSIS so let’s dive straight into it.
I started BIDS, created a new project FileManager and added a new package MoveFilesFromSoureToDestinationFolder. I then created 3 string variables in SSIS namely SourceFolder, DestinationFolder and FileName. I set the value of SourceFolder to the parent directory on my network where all my 1000 plus folders reside, and I set the value of the DesitnationFolder to the network folder where l want all the files to be placed. The FileName variable value was set to myfile.txt.
I dragged and dropped a foreach Loop Container from SSIS tools onto the control flow of the package, double click the foreach loop container and perform the following. I clicked on collection section in the foreach loop editor and set it to Foreach File Enumerator, then l clicked on the ellipse next to the expressions and it opened the property expression editor.
In the property expression editor, I chose directory under the property, clicked on the ellipse under expression which opens the expression builder. I then dragged the User::SourceFolder variable from the variables collection in the expression builder to the expression textbox in the expression builder. I clicked on the evalute button in the expression builder and it shows the folder path in the SourceFolder variable. I clicked ‘OK’ twice to get out of the expression editing to arrive at the foreach loop editor window.
In the Enumerator Configuration group box of the Foreach loop editor, l left the folder to point to C: as it is by default and also left the files to *.* as it is by default. If you want to move only some specific file types, then this is where to set it. For example, if you want to move only text files, then you have to set it to *.txt . Under retrieve filename section, l chose fully qualified because l want to pass the full path of any of the files to a variable which l will discuss next. I also check marked traverse subfolders so that once l arrive at the sourcefolder, the application will be able to go down any subfolder to get the files out of it and move it to the destination folder.
The next thing l did was clicked on the variable mappings section of the Foreach loop editor. clicked on the drop down menu under Variables, selected User::FileName and entered zero under index. What this means is, I want any file found in the source folder assigned to the variable FileName when the loop is scanning folders and sub folders in the source directory. I clicked ‘OK’ to complete setting up the foreach Loop container.
The next step was, l dropped a file system task from SSIS tools into the foreach Loop Container, double clicked it and perform the following settings. I clicked the general section of the File System edition then l set the following
IsDestionationPathVariable = True
DestinationVariable = User::DestinationFolder
Operation = Move File (set this to copy file if you want to copy the files and leave the original files at the source)
IsSourcePathVariable = True
SourcePathVariable = User::FileName
I clicked ‘OK’ to complete setting the file system task.
Then I execute the package by pressing F5 and it run successfully and turned green. When l look at the final destination, all the files were there as expected and all the folders in the source directory became empty as l perform a move operation.
This was sweet and made my day so l wanted to share so bad l had to wake up at 2 am from my sleep to post it on my blog. Going back to bed now!
Thanks for reading.
