How to load Data from Multiple Text / CSV Files to SQL Server Table with File Name by using Script Task in SSIS Package - SSIS Tutorial

Scenario: Download Script

You get tons of text or csv files in one of the Source Folder. You need to load all the files to SQL Server Table. All the flat files has the same number of columns. You would like to also load the file name with data to SQL Server Table. How would you do load file name with data to SQL server Table by using SSIS Package?


Here is our SQL Server Table 

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


Sample files for this demo shown below

Solution:

This can be done by using built-in Tasks, Transformations and expressions in SSIS Package. I have written this post already, you can take a look if you like
But in this post we are going to learn how to use script task to handle this situation. We will load the data to table and then archive the files to archive folder after adding datetime to it.


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 ('";
                     query += line.Replace(FileDelimiter, "','") + "','" + fileName + "')";
                            //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 with file names and move the file to archive folder after loading them.

Here are records loaded by package in dbo.Customer table from my sample files.
Import Text files to SQL Server Table with File Name in SSIS Package by using Script Task



Files are moved to Archive Folder, Date time was added to each file.
How to load file name with data to SQL Server Table when loading CSV Files in SSIS Package





 Related Posts / Videos on Dynamic Text / CSV files by Script Task 

9 comments:

  1. Hi, I keep getting the following error when I attempt to run this using SQL Authentication

    ExecuteNonQuery: Connection property has not been initialized.


    Any thoughts on how to fix this?

    ReplyDelete
    Replies
    1. Thanks for uploading these videos and sharing knowledge ! They are very descriptive and helpful. It worked for me for CSV files but i am trying to make them work for excel files. .XLS is the extension. What would be my file delimiter in that case ? Please suggest.

      Delete
  2. I tried with blank but package is failing.

    ReplyDelete
  3. Hi, Thank you so much for providing the code to load multiple flat files to SQL table. We are having the same use case in our organization. This is so helpful. I have tried using your process but in the sql table I am getting double quotes for each column. I know can use text qualifier to get rid of this if we use flat file task. How can we achieve this in script task. Could you please let me know.

    Appreciate your help.

    Thanks in advance.

    ReplyDelete
  4. Hello, I am trying to do Multiple CSV to multiple tables in SQL using c# script. But getting issues with datatypes , espically dattime issues, CSV datetime filed is of type string and in SQL its datetime so unable to convert string to datetime dynamic. Help me here please

    ReplyDelete
  5. How can this be done quicker? currently loading 1mil rows an hour....way too slow.

    ReplyDelete
  6. Hi, my csv file looks like the following
    Vehicle Loan,"TERIL NELSON",'215300','27-OCT-2020 15:22:12','27-OCT-20','MOBILE'

    when I clean it up manually like following with just the comma separator, the script works perfectly.
    Vehicle Loan,TERIL NELSON,215300,27-OCT-2020 15:22:12,27-OCT-20,MOBILE

    I think this line needs some fixing for my csv files to load, can someone please help?
    query += line.Replace(FileDelimiter, "','") + "','" + fileName + "')";

    Thanks,
    Faiz

    ReplyDelete
  7. I am thankful to you for sharing this plethora of useful information. I found this resource utmost beneficial for me. Thanks a lot for hard work. Legacy Data Archiving System

    ReplyDelete
  8. Thank you so much for posting this.

    Could you please let me know how to fix this error:
    Unclosed quotation mark after the character string

    The script works fine but it give that error at this record because it has the single quote ' in the address field:

    220615550139319,999,"WILLIAM","SMITH","34522498","8782","P","180 BURKETT'S FERRY RD","HAZELHURST",20220301,20220302,"GA","31539",0

    Thank you in advance for your help!

    ReplyDelete