Reading CSV file in SSIS Script Component task

Question

I have created a transformation to copy data from CSV file to SQL Server table. Since my CSV file is dynamic and can have any number of columns at given time, I included a ScriptComponent Task to maintain the mapping b/w 'FlatFileSource' and 'OLEDBDestination' task.

I am able to copy data now but the column names row is also getting added as a data row in destination table which I don't want. Below is the code I have written in script task to read csv and add rows to output buffer.

/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;
using System.Text.RegularExpressions;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    private StreamReader sr = null;


    public override void PreExecute()
    {
        base.PreExecute();
        /*
          Add your code here for preprocessing or remove if not needed
        */

        string sourceFile = Variables.FilePath;

        if (File.Exists(sourceFile))
        {
            try
            {
                sr = new StreamReader(sourceFile);
            }

            catch
            {

            }
        }
    }

    public override void PostExecute()
    {
        base.PostExecute();
        /*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        /*
          Add your code here
        */
    }

    public override void CreateNewOutputRows()
    {
        /*
          Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
          For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
        */

        if (sr != null)
        {
            while (!sr.EndOfStream)
            {
                string headline = sr.ReadLine();

                if (!string.IsNullOrEmpty(headline))
                {

                    //get columns value by comma as delimited and Double Quotation as text qualifier

                    //char[] param = {',', @'\'};

                    string[] columns = headline.Split(',');


                    Output0Buffer.AddRow();
                    Output0Buffer.Source = Variables.Source.ToString();
                    Output0Buffer.Column = columns.Length > 0 ? columns[0].ToString() : null;
                    Output0Buffer.Column1 = columns.Length > 0 ? columns[1].ToString() : null;
                    Output0Buffer.Column2 = columns.Length > 0 ? columns[2].ToString() : null;
                    Output0Buffer.Column3 = columns.Length > 0 ? columns[3].ToString() : null;
                    Output0Buffer.Column4 = columns.Length > 0 ? columns[4].ToString() : null;
                    Output0Buffer.Column5 = columns.Length > 0 ? columns[5].ToString() : null;
                    Output0Buffer.Column6 = columns.Length > 0 ? columns[6].ToString() : null;
                    Output0Buffer.Column7 = columns.Length > 0 ? columns[7].ToString() : null;
                    Output0Buffer.Column8 = columns.Length > 0 ? columns[8].ToString() : null;
                    Output0Buffer.Column9 = columns.Length > 0 ? columns[9].ToString() : null;
                    Output0Buffer.Column10 = columns.Length > 0 ? columns[10].ToString() : null;
                    Output0Buffer.Column11 = columns.Length > 0 ? columns[11].ToString() : null;
                    Output0Buffer.Column12 = columns.Length > 0 ? columns[12].ToString() : null;
                    Output0Buffer.Column13 = columns.Length > 0 ? columns[13].ToString() : null;
                    Output0Buffer.Column14 = columns.Length > 0 ? columns[14].ToString() : null;
                    Output0Buffer.Column15 = columns.Length > 0 ? columns[15].ToString() : null;
                    Output0Buffer.Column16 = columns.Length > 0 ? columns[16].ToString() : null;
                    Output0Buffer.Column17 = columns.Length > 0 ? columns[17].ToString() : null;
                    Output0Buffer.Column18 = columns.Length > 0 ? columns[18].ToString() : null;
                    Output0Buffer.Column19 = columns.Length > 0 ? columns[19].ToString() : null;
                    Output0Buffer.Column20 = columns.Length > 0 ? columns[20].ToString() : null;
                    Output0Buffer.Column21 = columns.Length > 0 ? columns[21].ToString() : null;
                    Output0Buffer.Column22 = columns.Length > 0 ? columns[22].ToString() : null;
                    Output0Buffer.Column23 = columns.Length > 0 ? columns[23].ToString() : null;
                    Output0Buffer.Column24 = columns.Length > 0 ? columns[24].ToString() : null;
                    Output0Buffer.Column25 = columns.Length > 0 ? columns[25].ToString() : null;
                    Output0Buffer.Column26 = columns.Length > 0 ? columns[26].ToString() : null;
                    Output0Buffer.Column27 = columns.Length > 0 ? columns[27].ToString() : null;
                    Output0Buffer.Column28 = columns.Length > 0 ? columns[28].ToString() : null;
                    Output0Buffer.Column29 = columns.Length > 0 ? columns[29].ToString() : null;
                    Output0Buffer.Column30 = columns.Length > 0 ? columns[30].ToString() : null;
                    Output0Buffer.Column31 = columns.Length > 0 ? columns[31].ToString() : null;
                    Output0Buffer.Column32 = columns.Length > 0 ? columns[32].ToString() : null;
                    Output0Buffer.Column33 = columns.Length > 0 ? columns[33].ToString() : null;
                    Output0Buffer.Column34 = columns.Length > 0 ? columns[34].ToString() : null;
                    Output0Buffer.Column35 = columns.Length > 0 ? columns[35].ToString() : null;
                    Output0Buffer.Column36 = columns.Length > 0 ? columns[36].ToString() : null;
                    Output0Buffer.Column37 = columns.Length > 0 ? columns[37].ToString() : null;
                    Output0Buffer.Column38 = columns.Length > 0 ? columns[38].ToString() : null;
                    Output0Buffer.Column39 = columns.Length > 0 ? columns[39].ToString() : null;
                    Output0Buffer.Column40 = columns.Length > 0 ? columns[40].ToString() : null;
                    Output0Buffer.Column41 = columns.Length > 0 ? columns[41].ToString() : null;
                    Output0Buffer.Column42 = columns.Length > 0 ? columns[42].ToString() : null;
                    Output0Buffer.Column43 = columns.Length > 0 ? columns[43].ToString() : null;
                    Output0Buffer.Column44 = columns.Length > 0 ? columns[44].ToString() : null;
                    Output0Buffer.Column45 = columns.Length > 0 ? columns[45].ToString() : null;
                    Output0Buffer.Column46 = columns.Length > 0 ? columns[46].ToString() : null;
                    Output0Buffer.Column47 = columns.Length > 0 ? columns[47].ToString() : null;
                    Output0Buffer.Column48 = columns.Length > 0 ? columns[48].ToString() : null;
                    Output0Buffer.Column49 = columns.Length > 0 ? columns[49].ToString() : null;

                }
            }
        }
    }

}

Please let me know if i need to put any more info from my ssis here. Thanks!!

Answer 1

The SSIS aspect doesn't really change anything to do with StreamReader reading the header, unless you want to do something meaningful with the header row.

Just call sr.ReadLine() prior to your loop (to read the first line) and do nothing with it, like so:

...
sr.ReadLine();
while (!sr.EndOfStream)
{
    string headline = sr.ReadLine();
    if (!string.IsNullOrEmpty(headline))
...