Welcome To TechBrothersIT: How to Import Multiple Text or CSV files to SQL Server Table by using Script Task in SSIS Package

Scenario: Download Script

You are working as ETL Developer / SSIS Developer. You get single or multiple text/csv files in Source Folder on daily basis. You want to create an SSIS Package that should be able to load all the files to single Table and archive after loading to table.

You want to built this package with configuration so by only provide different configuration values, it should be able to handle below scenarios

1) If your file provider decided to provide .txt file, it should be able to take them and load them

2) if your file provide decided to provide .csv files , by changing configuration value , package should be able to load the files

3) Table Name can change any time, so you should be able to provide table name by using configuration

4) Source Folder Path and Archive Folder Path can change, so you should be able to handle through configuration

5) File delimiter can also change, let's say you are getting files with comma(,). The file provider can make decision moving forward, he will be provide files with pipe( | ) .

Here is create script for my dbo.Customer table

Create table dbo.Customer(
Id INT,
Name VARCHAR(100),
Dob Date)

Sample Files in SourceFolder

Solution:

We can create an SSIS Package by using built-in Tasks, Transformations and Expressions. But I am moving forward with Script Task in this post. So we can learn some C# scripting language and also handle all the situation in Script Task.

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.

DestinationTable : Provide the Destination Table name with Schema as shown where you would like to load flat files( text files or csv files)

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.

Create Variable in SSIS Package to Load and Archive flat files by using Script Task -SSIS tutorial

Step 2:

Click in Connection Manager Pane and then Create ADO.NET Connection by providing Server Name and database Name. After creating I have renamed it to DBConn.

Create ADO.NET Connection so we can use in Script Task to load the data to Destination Table

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 so we can load multiple Text files or csv files to SQL Server Table 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 Table

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 TableName = Dts.Variables["User::DestinationTable"].Value.ToString();
          string ArchiveFolder = Dts.Variables["User::ArchiveFolder"].Value.ToString();
                //string ColumnList = "";

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

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

                    //Writing Data of File Into Table
                    int counter = 0;
                    string line;
                    //MessageBox.Show(fileName);

                    System.IO.StreamReader SourceFile =
                    new System.IO.StreamReader(fileName);
                    while ((line = SourceFile.ReadLine()) != null)
                    {
                        if (counter > 0)
                        {
                        
                            string query = "Insert into " + TableName + " 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: Save Script and Run SSIS Package

Save the script in Script Editor and execute your SSIS Package, It should load the files from Source folder to SQL Server table and move the file to archive folder after loading it.

Here are records loaded by package in dbo.Customer table from my sample files.