Recently I found myself redesigning an ETL Process for my client to improve performance. Ultimately the client receives hundreds of thousands of files daily that are pushed incrementally throughout the day. We wanted to take advantage of using the Multi Flat File connection manager for efficiently and quickly processing these files. So far so good right?

Not sure what the Multi Flat File Connection manager is? Check out my blog here:

First in order to really use this connection manager I needed to move the files from the original “landing zone” (directory where files are being pushed) to a controlled working directory. So how do I do this quickly and efficiently? Glad you asked . With an execute process task and the windows move command of course!

Walkthrough:

1. Create a new SSIS Package.
2. Pull the Execute Process Task into the control flow.
3. Open the Execute Process Task and click on the Process tab (on the left).
4. For executable point to your windows command prompt, found here:
   1. C:\Windows\System32\cmd.exe
5. Finally it’s time to issue our windows move command, here is the breakdown in 5 parts:
   1. /C – required when running windows commands from the Execute Process Task
   2. /move – Move file command, read more here:
   3. /Y – Suppresses prompting to confirm you want to overwrite an existing destination file.
   4. Source Directory with wild card filter: C:\Blogs\1000_Files_Test\Test*
   5. Destination Directory: C:\Blogs\1000_Files_Test_Move\

Thanks and enjoy!