Solved

SSIS: Validate Column Headers in CSV File

Posted on 2013-12-03
3
2,482 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

808 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