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 

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 

How to Load Text /CSV files with same or less columns than destination table by using Script Task in SSIS Package - SSIS Tutorial

Scenario: Download Script

We get multiple text of csv files from different clients those we need to load to our dbo.Customer table. Not a problem, we can create an SSIS Package that can load multiple text files to SQL Server table and we have already did by using this post.But there is little problem. Each client will not send exact number of columns what we have for our table.

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



Think about that client A sends always file with id, name and dob columns.
Client B can send file with Id and Name. Also Client B can send some files with three columns id,name and dob.
Client C can also send the file with only column Name and Dob or any other combination.

One thing to noticed here, the files can have same columns or less but will never have more columns than SQL Server table. We need to create an SSIS Package that should be able to load these type of files.

This package should be like template, that we can use for any type of related requirement and can change destination table name,Source folder, Archive Folder, File Extension and File Delimiter etc. by using SSIS Configuration.

Sample data files using for my demo. As you can see that I have three files each one has different combination of columns but all matches with columns  of SQL Server table.



Solution:

We will be using Script Task in SSIS Package to load all the text files to SQL Server table with same or less columns than table definition.

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;
                    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 " + 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 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.
Data load to Table from Multiple Text files with less or same Columns as Table definition in SSIS Package by using Script Task


 Files moved to Archive Folder after datetime added to them as shown below.
How to load CSV files with same or less columns to SQL server Table in SSIS Package




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


How to Import Multiple Text or CSV files to SQL Server Table by using Script Task in SSIS Package - SSIS Tutorial

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.
How to load multiple csv files to SQL Server Table by using Script Task in SSIS Package



 Files moved to Archive Folder after datetime added to them as shown below.
How to load multiple text files by using script task in ssis package and archive after loading





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