SSIS: Validate Column Headers in CSV File

Hi All,

I want to validate the headers from a CSV file that I am using in my Data Flow Task i.e. if the headers differ from what is expected.

How can I do this? I've searched and search and can't even find anything....

This looks like something relatively simple (at least in Excel/VBA or Access).

Thanks,

OS
onesegunAsked:
Who is Participating?
 
onesegunConnect With a Mentor Author Commented:
Hi JimHorn,

Thanks for your tip. I read it through and it seems to make sense but I thought it was the long way round so I found another solution using scrip task and modified it a bit. Seemed to me to be easier done like than having to jump through many hoops.


SSIS -How to Read First Row From Flat File [Header Row]

        public void Main()
        {
            using (StreamReader r=new StreamReader(Dts.Variables["User::FilePath"].Value.ToString()))
            {
                Dts.Variables["User::HeaderRow"].Value=r.ReadLine();
                MessageBox.Show(Dts.Variables["User::HeaderRow"].Value.ToString());       
            }

            if (Convert.ToString(Dts.Variables["User::HeaderRow"].Value) == "ID,Name,Address")
            //if (Dts.Variables["User::HeaderRow"].Value == "ID,Name,Address")
            {
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            else
            {
                Dts.TaskResult = (int)ScriptResults.Failure;
            }

            // TODO: Add your code here
            //Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

Open in new window


Thanks,

OS
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
SSIS doesn't have this ability, so afaik the only way to pull this off would be to...
Create a SQL table with an identity column to accept the csv

Truncate the table so that the identity column re-seeds to 1
import your CSV into the table that has an identity column, with the column headers imported as a row of data (i.e. not as column headers)
In T-SQL you can query the table ... WHERE identity_column = 1, which will be your header row, and validate all you want.

Hope this helps.
Jim
0
 
onesegunAuthor Commented:
A lot simpler to implement
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.