SSIS: Loop over folder and Archiving files


I want to move all the files saved in a certain folder.


I can achieve this using the

  1. Foreach loop container
  2. File system task/Script task

Script task code:
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;//added
using System.Text.RegularExpressions;//added
public void MoveFile()
/*rename file and move*/
string package_name = Dts.Variables[“User::package_name”].Value.ToString().Trim();
string file_diretory_name = Dts.Variables[“User::package_associated_filename”].Value.ToString().Trim();
string archive_directory = Dts.Variables[“User::package_archive_directory”].Value.ToString().Trim();
string file_name = “”;
Regex regex = new Regex(@”\w*[.]+\w*”);
Match match = regex.Match(file_diretory_name);
if (match.Success)
file_name = match.Value;

int pos = file_name.IndexOf(‘.’);
string filename_without_extension = file_name.Substring(0, pos);
string file_extension = file_name.Substring(pos);

string date_now = DateTime.Now.ToString(“_yyyyMMdd_HHmmss”).Trim();
string source_file = file_diretory_name;
string destination_file = archive_directory + filename_without_extension + date_now + file_extension;
System.IO.File.Copy(source_file, destination_file, true);


Leave a Reply

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

You are commenting using your 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