Move Files in SSIS Using execute process task

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 Winking smile. 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\

image

Thanks and enjoy!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s