How to Create Tables Dynamically from Flat Files and load Data in SSIS Package - SSIS Tutorial

Scenario: Download Script

You received flat files or text files or csv files in one of the source folder. You need to write an SSIS Package that should read the file columns and create table and load the data from file. Once data is loading move the file to archive folder.
The table will be created with name of file. If already exists, we would like to drop the table and created.

Solution:

We are going to use Script Task in this post to create table dynamically for each flat file and load 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.
ColumnsDataType : Provide the data type you would like to use for newly created table/s.
SchemaName : Provide the schema name in which you would like to create your table/s.
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 Variables in SSIS Package to Import Flat Files dynamically to Tables - 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 Tables 


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 Tables in SSIS Package


Step 4: Add Script to Script task Editor in SSIS Package to create tables dynamically and load data from flat files
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 ColumnsDataType = Dts.Variables["User::ColumnsDataType"].Value.ToString();
                string SchemaName = Dts.Variables["User::SchemaName"].Value.ToString();
                //string ColumnList = "";
                

                //Reading file names one by one
        string[] fileEntries = Directory.GetFiles(SourceFolderPath, "*" + 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
                    string TableName = "";
                    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, "],[") + "]";
         TableName = (((fileName.Replace(SourceFolderPath, "")).Replace(FileExtension, "")).Replace("\\", ""));
         string CreateTableStatement = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'["+SchemaName+"]."; 
         CreateTableStatement+="["+TableName + "]')";
         CreateTableStatement+=" AND type in (N'U'))DROP TABLE ["+SchemaName+"].";
         CreateTableStatement+= "["+TableName + "]  Create Table "+SchemaName+".[" + TableName+"]";
         CreateTableStatement+="([" + line.Replace(FileDelimiter, "] "+ColumnsDataType+",[") + "] "+ColumnsDataType+")";
         SqlCommand CreateTableCmd = new SqlCommand(CreateTableStatement, myADONETConnection);
         CreateTableCmd.ExecuteNonQuery();

         //MessageBox.Show(CreateTableStatement);

                        }
                        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: Save Script and Run SSIS Package
Save the script in Script Editor and execute your SSIS Package,It should create new table for each Flat file and then load the data.Once data loading is complete from flat file / csv file, the file will be moved to archive folder after adding datetime to it.
Dynamic table create and load data in ssis package from flat files / csv files - SSIS Tutorial



Files are archived after adding date time to them.
How to load and archive flat files dynamically in SSIS Package by using Script Task - SSIS tutorial






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

9 comments:

  1. this does not work with Tab Delimited Flat file. I have used the following
    Tab {t}
    {t}
    t
    what should it be thanks

    ReplyDelete
  2. how can we use bulk insert here ? Insert is very slow

    ReplyDelete
  3. I am getting error -> Cannot execute script because the script entry point is invalid.

    ReplyDelete
  4. I am getting the following error:
    Unclosed quotation mark after the character string ',',')'

    ReplyDelete
  5. Hello, this create "" around the columnname and data. This is how my table look now:
    "id" "UsrName" "RunDate" and the data shown as "1" "MickeyMouse" "03/01/2021" etc..... Can anyone tell me which line in the ##C do I change to not include the "" ? I'd tried these:
    // WRONG: CreateTableStatement += line.Replace(FileDelimiter, "] + ColumnsDataType + ,[") + ColumnsDataType ;
    // WRONG: CreateTableStatement += line.Replace(FileDelimiter, ColumnsDataType ) + ColumnsDataType ;
    got error: Incorrect syntax near 'ID'

    ReplyDelete
  6. Read this before you get started! There is an issue with this method one should be aware of. If the text contains a comma (qualified or not qualified) it will replace it with ',', thus throwing of the values. If your data does not have imbedded commas in the text, you're ok. If it does, don't use it.

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Hi, How do you make this safe from SQL injection ? Have tried with adding parameters but it doesn't work.

    ReplyDelete
  9. This is beautiful! Thank you so much. Tons of time save on a 160 txt file load. I had to mod a little bit to be able to handle data with single quotes but mostly just followed instructions and it worked great!

    ReplyDelete