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.





3 comments:

  1. I feel SSIS is the best and most useful tool to solve complex IT problems.On the other hand there is a need to bring in the most complex tool.

    SSIS Postgresql Write

    ReplyDelete
  2. I HAVE TWO DIFFERNT TABLES AND LOAD FILES ACCORDING TO THAT TABLES AND MY ALL FILES PUT IN ONE FOLDER.
    FOR E.X IN MY FOLDER I AHVE FILES NAMES LIKE PRED_F,PRED_F1,PRED_F2 THESE FILES I HAVE TO LOAD IN ON E TABLE AND FILES PRED_J IN ANOTHER TABLE I USE FOR EACH LOOP WITH TWO DATA FLOW TASK WITH SCRIPT TASK AND WITH EXPERSSION I READ TOHSE FILE BUT EXPERSSION EVALUTED FALSE.PLEASE SUGGEST ME A SOLUTION.

    ReplyDelete
  3. You know. I don't often work with different files, so when I urgently needed to merge PDF files into one, I hesitated. But I managed to find this convenient and free online tool https://www.pdfplatform.com/merge which helped me solve the issue of merging pdf files quite quickly and easily. Now I will use PDF files more often. because I understood how to work with it.

    ReplyDelete