Validating the CSV data before inserting into SQL Server

I have a senario where i have to check the first column of the excel has a valid data and not in the other columns from the CSV file. If the data is not present in the first column my SSIS package should log an exeption.

开发者_如何学运维Can any one help me in this senario please.

Thanks, Sateesh.

In SSIS you can use a conditional Split task to do this and send the good data to where you want it and the bad data to an exception table.

Personally I always prefer to start with putting the data for any import into two tables, one with the raw unchanged data and one that will contain the cleaned up data before the import to the prod tables. This makes it easier to see what the cause is when you inevitably have to research why some bad data got into the database (if you are doing your job right, 90+% of the time it's bad data you were sent _ you can't know the contract expires on 4/12/2012 when you were sent 4/12/2011 to pick a not so random example). Also always make sure to save the input file to an archive loaction. Trust me, you will need one or more of those archived files some day.