Load the CSV files which have dynamic columns using Script task

We are getting the CSV files from multiple vendors, some vendors are sending all column and some vendors are sending only Mandatory columns. We need to read the column name and load it into the database.

See below are the files.  

This is the correct file. But sometimes we are getting files with missing columns like below.   

In this file we are getting two missing columns ( TotalDiscount and DeliveryCharge). In this case our package is getting failed.

We can load both file using Script task without failing this package.

In this Demo we will see how we are loading these files.

Note : File Columns name and Tables Columns name show be same. Otherwise it will throw an error.

Taking the script task.

Our source folder as below.

   

Taking Script task and writing the below code.

public void Main()                                 {             string SourceFolderPath = @"H:\SSIS1\Source\Source_File";                      string FileDelimiter = ",";             string TableName = "Invoices";             try             {                 //Get files from folder                 string[] fileEntries = Directory.GetFiles(SourceFolderPath);                 foreach (string fileName in fileEntries)                 {                     //Create Connection to SQL Server                     SqlConnection SQLConnection = new SqlConnection();                     SQLConnection.ConnectionString = "Data Source = BAGESH\\BAGESHDB; Initial Catalog =TestDB; Integrated Security=true;";                     int counter = 0;                     string line;                     string ColumnList = "";                     System.IO.StreamReader SourceFile = new System.IO.StreamReader(fileName);                     SQLConnection.Open();                     while ((line = SourceFile.ReadLine()) != null)                     {                         if (counter == 0)                         {                             //By using Header Row, Build Column List                             ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "]";                             ColumnList= ColumnList+",[File_nm]";  //Adding File name in the columns                         }                         else                         {                               //Build and Execute Insert Statement to insert record                             string query = "Insert into " + TableName + " (" + ColumnList + ") ";                             query += "VALUES('" + line.Replace(FileDelimiter, "','") +"','"+ fileName + "')";                               SqlCommand SQLCmd = new SqlCommand(query, SQLConnection);                             SQLCmd.ExecuteNonQuery();                         }                         counter++;                     }                       SourceFile.Close();                     SQLConnection.Close();                                    }             }             catch (Exception ex)             {                 throw ex;               }         }

Now package is ready to run.

See the records in the table.

  

Running this package. 

Package executed successfully. Records in the table.  

Data loaded successfully without package fail.