Welcome To TechBrothersIT: How to load Flat files to SQL Server Tables according to the Name of Flat File in SSIS Package

How to load Flat files to SQL Server Tables according to the Name of Flat File in SSIS Package - SSIS Tutorial

Scenario : Download Script

You are working as ETL Developer or SQL Server Integration Services developer. You get multiple files on daily basis in your Source folder. The files comes with date and some of them comes with date and time. You need to extract Name from file and then load to table accordingly. Each file Name does match with table Name.

How to load flat files to SQL Server Tables according to the name of file in SSIS Package by using Script Task

Here is script that I used to create tables for above sample files.

Create table dbo.Customer
(Id int, 
Name varchar(100)
)
GO

Create Table dbo.TechBrothersInfo
(
Id int, 
CourseTitle VARCHAR(100)
)

Solution:

We are going to use Script Task with C# scripting language in our SSIS Package to load the files from source folder to sql server tables according to the names. we will also archive ( move) the files once loaded.

Step 1: Create New SSIS Package and Variables

Open SSDT ( SQL Server Data Tools) and create new SSIS Package. After that go ahead and create variables so we can use them in configuration to pass values anytime our requirement change.

ArchiveFolder: Provide the folder path where you would like to move files after loading. Datetime part will be added to file name.

FileDelimiter : Provide the delimiter which is used in your txt or csv files.

FileExtension : Provide the Extension of files you would like to load from folder.

LogFolder : Provide the folder path where you would like to create log file in case of error in script task

SourceFolder: Provide the source folder path where text files or csv files are places for import process.

SchemaName : Provide the schema in which tables are present. In my case all the tables exists in dbo schema.

Create variables in SSIS Package to load flat files dynamically to sql server tables according to file name

Step 2: Create ADO.NET Connection in SSIS Package to use in Script Task

Create ADO.NET Connection Manager so we can use in Script Task to Load data from flat files to SQL Server Tables.

Create ADO.NET Connection in SSIS Package to use in Script Task to load flat files to sql server tables according to file names

Step 3: Add Variables to Script Task to use from SSIS Package

Bring the Script Task on Control Flow Pane in SSIS Package and open by double clicking Check-box in front of variable to add to Script Task.

Add variables to Script Task to load files to sql server table as per file name in SSIS Package

Step 4: Add Script to Script task Editor in SSIS Package to import multiple CSV files or Text Files to SQL Server Tables according to the name of file

Click Edit Button and it will open Script Task Editor.

Under #region Namespaces, I have added below code

using System.IO;
using System.Data.SqlClient;

Under public void Main() {

I have added below code.

            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            try
            {

                //Declare Variables
           string SourceFolderPath = Dts.Variables["User::SourceFolder"].Value.ToString();
           string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();
           string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
           string ArchiveFolder = Dts.Variables["User::ArchiveFolder"].Value.ToString();
           string SchemaName= Dts.Variables["User::SchemaName"].Value.ToString();


           SqlConnection myADONETConnection = new SqlConnection();
           myADONETConnection = (SqlConnection)
           (Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);


                //Reading file names one by one
           string SourceDirectory = SourceFolderPath;
           string[] fileEntries = Directory.GetFiles(SourceDirectory, "*" + FileExtension);
                foreach (string fileName in fileEntries)
                {

                    string TableName = "";
                    //Remove All Numbers and other characters and leave alphabets for name
                    System.Text.RegularExpressions.Regex rgx = new System.Text.RegularExpressions.Regex("[^a-zA-Z]");
                    TableName = rgx.Replace(fileName.Replace(SourceDirectory, "").Replace(FileExtension, ""),"");
                   // MessageBox.Show(TableName);
                    //Writing Data of File Into Table
                    int counter = 0;
                    string line;
                    string ColumnList = "";
                    //MessageBox.Show(fileName);

                    System.IO.StreamReader SourceFile =
                    new System.IO.StreamReader(fileName);
                    while ((line = SourceFile.ReadLine()) != null)
                    {
                        if (counter == 0)
                        {
                            ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "]";

                        }
                        else
                        {
       string query = "Insert into "+SchemaName+"." + TableName + " (" + ColumnList + ") ";
                         query += "VALUES('" + line.Replace(FileDelimiter, "','") + "')";

                            // MessageBox.Show(query.ToString());
                        SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection);
                            myCommand1.ExecuteNonQuery();
                        }

                        counter++;
                    }

                    SourceFile.Close();
                    //move the file to archive folder after adding datetime to it
                    File.Move(fileName, ArchiveFolder + "\\" + 
                        (fileName.Replace(SourceFolderPath,"")).Replace(FileExtension,"") 
                        + "_" + datetime+FileExtension);
                    Dts.TaskResult = (int)ScriptResults.Success;
                }
            }
            catch (Exception exception)
            {
                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(Dts.Variables["User::LogFolder"].Value.ToString()
                    + "\\" + "ErrorLog_" + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }

            }

Step 5: Run SSIS Package to Load flat files Data to SQL Server table/s according to files Name

Save the script and Close the Script Task Editor. Run your SSIS Package.It should load the data from flat files to SQL Server tables according to the file names.

After loading the files , the files should be moved to archive folder. I tested and it load the files data successfully to my tables as shown below.