Solved

SSIS: Validate Column Headers in CSV File

Posted on 2013-12-03
3
2,325 Views
Last Modified: 2016-02-10
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
0
Comment
Question by:onesegun
  • 2
3 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 39692323
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
 

Accepted Solution

by:
onesegun earned 0 total points
ID: 39693014
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
 

Author Closing Comment

by:onesegun
ID: 39704108
A lot simpler to implement
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now