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.

How to load csv/ txt files to SQL Server Tables according to name of files in SSIS Package




Video Demo: How to Load CSV files to SQL Server Table as per name of file



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

8 comments:

  1. Excellent Piece of information ...Very useful content and it really helped me solve my queries ..You have done a phenomenal work..Thanks...

    Python Training in Chennai
    Data Science Training in Chennai
    Devops Training in Chennai
    Artificial Intelligence Training in Chennai
    Selenium Training in Chennai

    ReplyDelete
  2. Getting this error, think the problem is my files are tab delimited and Character fields are quoted "", could you suggest a workaround? Thanks!

    System.Data.SqlClient.SqlException (0x80131904): The identifier that starts with '200 "20088888" "88888 Direct Returns EU " "88888" "UNKNOWN" "200999" "UK NON-MERCHANDISE EU " "999" "200999" "UK NON-MERCHAND' is too long. Maximum length is 128.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    at ST_329fed8be5864a2283b99045c8c9116b.ScriptMain.Main()
    ClientConnectionId:9bb1bd94-61f6-4365-84e2-7f107be685c0
    Error Number:103,State:4,Class:15

    ReplyDelete
  3. Another issue, the script is replacing the underscores in the file name which should be the table name.
    Invalid object name 'dbo.ADISITEID' should be ADI_SITE_ID, my file and table are both name correctly.

    ReplyDelete
    Replies
    1. [a-zA-Z0-9_] fixing this issue with this in script

      Delete
    2. when I do that I get this System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '\'.

      Delete
  4. How can I download the source text files?

    ReplyDelete