Solved

SSIS: Validate Column Headers in CSV File

Posted on 2013-12-03
3
2,663 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 66

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

688 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