SSIS: How to Loop through multiple files and move them in different folders?

SSIS is a great tool to extract and transform data . In a business environment there could be multiple sources of data. The data supplied could be in different format and in different files. In a situation you are dealing with multiple files and want to move each type of file in a designated folder before extracting data from them SSIS helps you to achieve this. In this post we will learn how we can deal with this situation.

Let us consider a business scenario where you are receiving data from multiple vendors and each one send them in a different file. Some vendors send their data in a text file while others prefer to send it in Excel and some in CSV files. Before you process the data you have received a good practice is to segregate the files in different folders so that it becomes easy to handle them.

Considering this scenario we have follwing files(screenshot) available and each one of them contains data. We have some Text files, some Ms-Excel 2003 files, some CSV files and some Excel 2007 files. Let us say each one of our vendor stores the data in this locaiton.

• SSIS: Read public data of Twitter Users?
• SSIS: How to pull Tweets from Twitter?
• SSIS: How to pull Currency Rates from European Central Bank
• SSIS: How to pull Stock Quotes from Google Finance
• SSIS: How to load data into SQL table from Google Spreadsheet?
• SSIS: How to Download images from Picasa album?
• SSIS: How to Load images into Picasa web album?

We want to move each file in their respective folder. We have created a Text Files folder where we will move all the text files. A Excel file folder where we will move all the excel files. We have a CSV Files folder where we will move all the CSV files.

We want to achieve this solution through SSIS. I launched the SSIS Designer and created one SSIS package to do this. I followed the following steps during the design.

Step 1: I created four Package level variables. The purpose of thes variables to to store the folder destination location for Text, Excel and CSV files.

Step 2: I have created one Foreach Loop Container task and one Script Task on Control Flow tab.

Step 3: On the Foreach Loop Editor box I have selected Foreach File Enumerator and selected the folder location where all files are stored by vendors. From the Retrieve file name I checked the Fully Qualified name

Step 4: From the Variable Mappings options on Foreach Loop Editor I have mapped the strSourceFile variable which we created in step 1.

Step 5: After setting up the properties and configuration for Foreach Loop I moved to Script task. From the Script task editor box I selected all the variables which I created in step 1 in ReadOnlyVariables

I have added following code snippet in the Script Task. For the simplicity purpose I have removed the error handing and other codes generated by BIDS.

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

namespace ST_17e0465156064890a89659496279971d.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        public void Main()
        {
            if(System.IO.Path.GetExtension(Dts.Variables["strSourceFile"].Value.ToString())==".xls") 
            {
                System.IO.File.Move(Dts.Variables["strSourceFile"].Value.ToString(), Dts.Variables["strExcelDest"].Value.ToString() + System.IO.Path.GetFileName(Dts.Variables["strSourceFile"].Value.ToString()));
            }
            else if (System.IO.Path.GetExtension(Dts.Variables["strSourceFile"].Value.ToString()) == ".xlsx")
            {
                System.IO.File.Move(Dts.Variables["strSourceFile"].Value.ToString(), Dts.Variables["strExcelDest"].Value.ToString() + System.IO.Path.GetFileName(Dts.Variables["strSourceFile"].Value.ToString()));
            }
            else if (System.IO.Path.GetExtension(Dts.Variables["strSourceFile"].Value.ToString()) == ".txt")
            {
                System.IO.File.Move(Dts.Variables["strSourceFile"].Value.ToString(), Dts.Variables["strTXTDest"].Value.ToString() + System.IO.Path.GetFileName(Dts.Variables["strSourceFile"].Value.ToString()));
            }
            else if (System.IO.Path.GetExtension(Dts.Variables["strSourceFile"].Value.ToString()) == ".csv")
            {
                System.IO.File.Move(Dts.Variables["strSourceFile"].Value.ToString(), Dts.Variables["strCSVDest"].Value.ToString() + System.IO.Path.GetFileName(Dts.Variables["strSourceFile"].Value.ToString()));
            }
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

After setting up the task in SSIS I run it and SSIS package runs successfully and it moved all the files in their respective location.

This is not the only solution to solve business situations like this but you can try this and extend this solution as per your need. You can check the folders existence before moving the files and if folders are not there you can create them as well.