Load the CSV files which have dynamic columns using Script Component 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 component 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 the Foreach loop container to iterate the files.

Inside the loop we are taking the Data Flow task.

  

Configuring the files.

Now we are creating the Flat file Connection manager and mapping this connection string to the file full qualifier name.  We are setting this value using expression.  

We are setting this value using expression.  

Now taking Data flow task.

Taking the sources as Script component task and creating the connection.

 

Now we are creating the output columns.  

Now in Click on the Script and write the below code.

private StreamReader SR;     private string File1;       public override void AcquireConnections(object Transaction)     {         // Get the connection for File1         IDTSConnectionManager100 CM = this.Connections.FFC;         File1 = (string)CM.AcquireConnection(null);     }       public override void PreExecute()     {         base.PreExecute();         base.PreExecute();         SR = new StreamReader(File1);     }       /// <summary>     /// This method is called after all the rows have passed through this component.     ///     /// You can delete this method if you don't need to do anything here.     /// </summary>     public override void PostExecute()     {         base.PostExecute();              base.PostExecute();         SR.Close();     }       public override void CreateNewOutputRows()     {                 // Declare variables         string nextLine;         string[] columns;         char[] delimiters;                 string ID =string.Empty;         string InvoiceNumber= string.Empty;         string ClientID= string.Empty;         string TotalDiscount= string.Empty;         string DeliveryCharge=string.Empty;           // Set the delimiter         delimiters = ",".ToCharArray();           // Read the first line (header)         nextLine = SR.ReadLine();           // Split the line into columns         columns = nextLine.Split(delimiters);         int i = columns.Length;         int k = 1;           if (k<=i)             ID = columns[0].ToString();         k++;           if (k <= i)             InvoiceNumber = columns[1].ToString();         k++;         if (k <= i)             ClientID = columns[2].ToString();         k++;         if (k <= i)             TotalDiscount = columns[3].ToString();         k++;         if (k <= i)             DeliveryCharge = columns[4].ToString();           nextLine = SR.ReadLine();         while (nextLine != null)         {                         columns = nextLine.Split(delimiters);             {                                 Output0Buffer.AddRow();                 if (ID.Equals("ID"))                 Output0Buffer.ID = columns[0].ToString();                 if (InvoiceNumber.Equals("InvoiceNumber"))                     Output0Buffer.InvoiceNumber = columns[1].ToString();                 if (ClientID.Equals("ClientID"))                     Output0Buffer.ClientID = columns[2].ToString();                 if (TotalDiscount.Equals("TotalDiscount"))                     Output0Buffer.TotalDiscount = columns[3].ToString();                 if (DeliveryCharge.Equals("DeliveryCharge"))                   Output0Buffer.DeliveryCharge = columns[4].ToString();                   Output0Buffer.FileNm = File1.ToString();               }             nextLine = SR.ReadLine();         }     }

 Now taking OLEDB as a destination and creating the connect and doing the mapping.  

Now our package is ready to run.

See the records in the table.

  

Now running this package.  

Package executed successfully.

See the records in the tale.

 

Data loaded successfully.