Get File Column Count using SSIS C# Script Task

Sometimes you have changing schema in your flat file (Columns added or Removed)

If you ever want to know how many columns you have in flat file then simply use below snippet and it will tell you column count.

How to count columns in Flat file using C# in SSIS

How to count columns in Flat file using C# in SSIS

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion

namespace ST_e266a0b09e3c48eea5a73ff6fe09a353
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            var colCnt = GetFileColumns("file1");
            MessageBox.Show("Your file has total " + colCnt + " columns");
            Dts.TaskResult = (int)ScriptResults.Success;
        }
        private int GetFileColumns(string connName,char colSeperator= ',')
        {
            var filePath=Dts.Connections[connName].ConnectionString;
            string firstline;
            using (System.IO.StreamReader sr = new System.IO.StreamReader(filePath))
            {
                 firstline= sr.ReadLine();
            }              
            var colArr=firstline.Split(new char[]{colSeperator});
            return colArr.Length;
        }
        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }
}

 

Binary World is a Software Development company located in Atlanta, USA (since 2007). Binary World specialized in Business Intelligence, mobile, cloud computing and .Net Application Development.

Tagged with: , , ,
Posted in SSIS