title: Importing a File with Dynamic Columns
body: <p>I am new to SSIS and C#. In SQL Server 2008 I am importing data from a .csv file. Now I have the columns dynamic. They can be around 22 columns(some times more or less). I created a staging table with 25 columns and import data into it. In essence each flat file that I import has different number of columns. They are all properly formatted only. My task is to import all the rows from a .csv flat file including the headers. I want to put this in a job so I can import multiple files into the table daily.</p>
  
  <p>So inside a for each loop I have a data flow task within which I have a script component. I came up(research online) with the C# code below but I get error:</p>
  
  <p><strong>Index was outside the bounds of the array.</strong></p>
  
  <p>I tried to find the cause using MessageBox and I found it is reading the first line and the index is going outside the bounds of the array after the first line.</p>
  
  <p>1.) I need your help with fixing the code</p>
  
  <p>2.) My File1Conn is the flat file connection instead I want to read it directly from a variable <strong>User::FileName</strong> that my foreach loop keeps updating. Please help with modifying the code below.</p>
  
  <p><strong>Thanks in advance.</strong></p>
  
  <p>This is my flat file:</p>
  
  <p><a href="https://drive.google.com/file/d/0B418ObdiVnEIRnlsZFdwYTRfTFU/view?usp=sharing" rel="nofollow">https://drive.google.com/file/d/0B418ObdiVnEIRnlsZFdwYTRfTFU/view?usp=sharing</a></p>
  
  <pre><code>using System;
  using System.Data;
  using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
  using Microsoft.SqlServer.Dts.Runtime.Wrapper;
  using System.Windows.Forms;
  using System.IO;
  
  [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
  public class ScriptMain : UserComponent
  
  {
   private StreamReader SR;
   private string File1;
  
  public override void AcquireConnections(object Transaction)
  {
      // Get the connection for File1
      IDTSConnectionManager100 CM = this.Connections.File1Conn;
      File1 = (string)CM.AcquireConnection(null);
  }
  
  public override void PreExecute()
  {
      base.PreExecute();
      SR = new StreamReader(File1);
  }
  
  public override void PostExecute()
  {
      base.PostExecute();
      SR.Close();
  }
  
  public override void CreateNewOutputRows()
  {
      // Declare variables
      string nextLine;
      string[] columns;
      char[] delimiters;
      int Col4Count;
      String[] Col4Value = new string[50];
  
      // Set the delimiter
      delimiters = ";".ToCharArray();
  
      // Read the first line (header)
      nextLine = SR.ReadLine();
  
      // Split the line into columns
      columns = nextLine.Split(delimiters);
  
      // Find out how many Col3 there are in the file
      Col4Count = columns.Length - 3;
      //MessageBox.Show(Col4Count.ToString());
  
      // Read the second line and loop until the end of the file
      nextLine = SR.ReadLine();
  
      while (nextLine != null)
      {
  
          // Split the line into columns
          columns = nextLine.Split(delimiters);
          {
              // Add a row
              File1OutputBuffer.AddRow();
  
  
              // Set the values of the Script Component output according to the file content
              File1OutputBuffer.SampleID = columns[0];
              File1OutputBuffer.RepNumber = columns[1];
              File1OutputBuffer.Product = columns[2];
              File1OutputBuffer.Col1 = columns[3];
              File1OutputBuffer.Col2 = columns[4];
              File1OutputBuffer.Col3 = columns[5];
              File1OutputBuffer.Col4 = columns[6];
              File1OutputBuffer.Col5 = columns[7];
              File1OutputBuffer.Col6 = columns[8];
              File1OutputBuffer.Col7 = columns[9];
              File1OutputBuffer.Col8 = columns[10];
              File1OutputBuffer.Col9 = columns[11];
              File1OutputBuffer.Col10 = columns[12];
              File1OutputBuffer.Col11 = columns[13];
              File1OutputBuffer.Col12 = columns[14];
              File1OutputBuffer.Col13 = columns[15];
              File1OutputBuffer.Col14 = columns[16];
              File1OutputBuffer.Col15 = columns[17];
              File1OutputBuffer.Col16 = columns[18];
  
  
          }
  
          // Read the next line
          nextLine = SR.ReadLine();
  
      }
  }
  
  }
  </code></pre>