How to create Tables Dynamically from Tab Delimited Files and Load data in SSIS Package - SSIS Tutorial

Scenario: Download Script

I wrote a post / video in which we learnt How to load flat files ( comma or pipe) delimited dynamically to SQL server Tables. Here is the link.

I received an email from one of the viewer and she was interested to load Tab Delimited files dynamically to SQL server Tables. The Package should be able to read the files from source folder, create the table after reading first row( header) and then load the data.

sample file

How to load Tab Delimited File dynamically to SQL Server Table

Solution:

The below package can be used to load comma, Pipe and Tab delimited. When need to use the Package for Tab delimited files, you have to set FileDelimiter variable value=TAB, if loading comma delimited files then provide comma.

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 that can be , or | or TAB.

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.

How to load Tab delimited dynamically to SQL Server Tables in SSIS Package

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 = "";
                if (FileDelimiter == "TAB")
                    {
                    FileDelimiter = "\t";
                }


                //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
                        {

                            //if data contains single quote, take care of that
                            line = line.Replace("'", "''");
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;
                }

            }

I executed the package and it was able to able to create new table by reading header row from tab delimited file and load the data.

How to load Tab Delimited Files dynamically to SQL server Tables in SSIS Package