Data Validation in SSIS

Question

Data Validation in SSIS

Lalitha Reddy

SSC Enthusiast

Points: 174
More actions
January 23, 2008 at 1:12 am

#378125

I have a CSV file,i need to transfer this to a database table in SSIS and then check for data i.e one of the column in my table has repeated data ,incase the data differs then i need to fail the data validation
Table Description
Name STATE CITY
aa US NY
bb US PY
cc US CA
dd US AL
ee US MA
ff US ST
In the above table for column STATE it has to contain only US, in case there is a differing data like USA then fail data validation.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Norman Kelm SSCommitted Points: 1922 More actions · Answer 1

Does the data need to be loaded in the table first?

The Conditional Split Transform can be configured to check to the column value = US and only load those rows. The rows that do not match that criteria can be sent to another table.

Norman

DTS Package Search

http://www.dtspackagesearch.com/

//Ravi SSCrazy Points: 2612 More actions · Answer 2

Lalitha,

I find it easier to first load the whole file (in yoru case, CSV file) into the table and then check for data validation. You can use a simple sql task in SSIS to check for rows with data. For example:

DELETE * FROM tablename where STATE <> 'US'

Ravi.

------------
🙂

Michael Ross-468051 Say Hey Kid Points: 689 More actions · Answer 3

I'm not sure what your question is. Have you worked around your issue?

nakkeeran.kannan Mr or Mrs. 500 Points: 503 More actions · Answer 4

http://arookiebidev.blogspot.com/2011/06/validation-in-ssis-package.html

I wrote a blog on data validation in SSIS package using script component.Hope this will be of some use to you.

Cheers,

NK

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

nakkeeran.kannan (6/23/2011)
http://arookiebidev.blogspot.com/2011/06/validation-in-ssis-package.html
I wrote a blog on data validation in SSIS package using script component.Hope this will be of some use to you.
Cheers,
NK

Your blog describes data validation on data types, not on the actual content of the data which was the original question (3 years ago).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

nakkeeran.kannan Mr or Mrs. 500 Points: 503 More actions · Answer 6

I have validated the data in the package.

If you had noticed I have validated the area code for the expression

@"\(?\s*\d{3}\s*[\)\.\-]?\s*\d{3}\s*[\-\.]?\s*\d{4}"

similarly we can validate any column and the redirect the rows accordingly using script component

example :

country == 'US'