?
Solved

SSIS: Validate Column Headers in CSV File

Posted on 2013-12-03
3
Medium Priority
?
2,983 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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

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…
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

750 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