SSIS - How To Load Files in Different Tables According To The Name Of File

Scenario:

We receive different text files in our source directly. For each of the file we have created the table. By anytime we get a file. We want to execute our SSIS package and load that file to related table. New files can be included moving forward. Whenever a new file will be added to the process, Related required table will be created ahead of time. We want to create an SSIS Package that can load the file according to the file name and we don't have to make any modification in SSIS Package if we want to include new file in process.

Sample Source Files With Different Columns

Solution:

We will be using Script Task to perform this task. 

Step1 :

Create a variable VarFolderPath that will hold your folder path for source files.

Fig 1: Create Variable for Source File Directory

Step 2:

Create ADO.NET Connection Manager to the database in which your tables exist.

Fig 2: Create ADO.NET Connection Manager in SSIS Package

After creating the connection, I have renamed it to ADO_TestDB as shown in Fig 2.

Step 3:

Bring the Script Task and map the VarFolderPath variable as shown below.

Fig 3: Map Variable To Script Task 

Once done, Click on Edit Script and use below code. I have bold the code that I included in Script Task.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;#endregion
namespace ST_abbc60b70dbb46c295e5cfad132a08e3
{
    /// 

    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// 
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
      
        public void Main()
        {
            // TODO: Add your code here

SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)(Dts.Connections["ADO_TestDB"].AcquireConnection(Dts.Transaction) as SqlConnection);
           // MessageBox.Show(myADONETConnection.ConnectionString, "ADO_TestDB");

                      
            //Reading file names one by one
            string SourceDirectory =Dts.Variables["User::VarFolderPath"].Value.ToString();
            // TODO: Add your code here
            string[] fileEntries = Directory.GetFiles(SourceDirectory);
            foreach (string fileName in fileEntries)
            {
                // do something with fileName
               // MessageBox.Show(fileName);
                string columname = "";


                //Reading first line of each file and assign to variable
                System.IO.StreamReader file2 =
                new System.IO.StreamReader(fileName);

                string filenameonly = (((fileName.Replace(SourceDirectory, "")).Replace(".txt", "")).Replace("\\", ""));

                file2.Close();

                //Writing Data of File Into Table
                int counter = 0;
                string line;

                System.IO.StreamReader SourceFile =
                new System.IO.StreamReader(fileName);
                while ((line = SourceFile.ReadLine()) != null)
                {

                    if (counter == 0)
                    {
                        columname = line.ToString();
                       
                    }

                    else
                    {
                      
                        string query = "Insert into dbo." + filenameonly + "(" + columname + ") VALUES('" + line.Replace(",", "','") + "')";
                        //MessageBox.Show(query.ToString());
                        SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection);
                        myCommand1.ExecuteNonQuery();
                    }
                    counter++;
                    }
                 SourceFile.Close();
            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        #region ScriptResults declaration
     
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }
}

Let's run our SSIS Package and see if the Tables are loaded.

Fig 4: Load Data to Tables from Flat File According to Name of File

As we can see that the data is loaded to each table from required file.