Validate only single date filed in the file in ssis

Question

Validate only single date filed in the file in ssis

p.shabbir

SSCrazy

Points: 2404
More actions
July 7, 2015 at 3:40 pm

#398711

Hi All,
Can anybody help me please on how to validate only single date filed in the file.
For example I have data in the file like below
IDNameDOB
1AAA07/07/1983
2BBB01/05/1988
3CCC07/12/2000
4DDD12/01/2001
5EEE20:30
6FFF11/26/2003
7GGG27/10/2004
8HHH12121980
9III20.30
10JJJ20051201
11KKKNULL
I have to validate only DOB column (ignoring ID, Name columns) and if DOB filed have value like 20:30,20.30 (5th & 9th records) then reject the file and send a notification.
Please let me know how to read only one column and validate it.
Appreciate your help.
Thanks In advnc.

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

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

It depends.

The best way would be to have your DOB column in your table as a date data type. This will validate that the data is correct and generate an error which you can use to clean the staging table and send the notification.

You can also define that column as a date in the connection properties for the file and get the error in there.

Either way, you're reading the whole file and validating the column. I hope that this is clear.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

p.shabbir SSCrazy Points: 2404 More actions · Answer 2

Luis Cazares (7/7/2015)
It depends.
The best way would be to have your DOB column in your table as a date data type. This will validate that the data is correct and generate an error which you can use to clean the staging table and send the notification.
You can also define that column as a date in the connection properties for the file and get the error in there.
Either way, you're reading the whole file and validating the column. I hope that this is clear.

Thanks for the response Luis. Will try that.

p.shabbir SSCrazy Points: 2404 More actions · Answer 3

You can also define that column as a date in the connection properties for the file and get the error in there.

Either way, you're reading the whole file and validating the column. I hope that this is clear.[/quote]

Luis,

I tired by changing the datatype as date in the flat file source connection manager and it failed saying data conversion error.

Actually I want to validate the date field in the file before loading the data to staging table. Is there any script (Script task) to valdiate. Based on the validated result from script I will decide whether total file should be rejected or process.

Thanks again.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

p.shabbir (7/9/2015)
You can also define that column as a date in the connection properties for the file and get the error in there.
Either way, you're reading the whole file and validating the column. I hope that this is clear.
Luis,
I tired by changing the datatype as date in the flat file source connection manager and it failed saying data conversion error.
Actually I want to validate the date field in the file before loading the data to staging table. Is there any script (Script task) to valdiate. Based on the validated result from script I will decide whether total file should be rejected or process.
Thanks again.

You're getting an error and you can work around that error using error handlers.

Using a script task is possible, but you'll be reading the file twice instead of once. Why would you want to add the additional step? That's why you're using a staging table.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

p.shabbir SSCrazy Points: 2404 More actions · Answer 5

Luis Cazares (7/9/2015)
p.shabbir (7/9/2015)
You can also define that column as a date in the connection properties for the file and get the error in there.
Either way, you're reading the whole file and validating the column. I hope that this is clear.
Luis,
I tired by changing the datatype as date in the flat file source connection manager and it failed saying data conversion error.
Actually I want to validate the date field in the file before loading the data to staging table. Is there any script (Script task) to valdiate. Based on the validated result from script I will decide whether total file should be rejected or process.
Thanks again.
You're getting an error and you can work around that error using error handlers.
Using a script task is possible, but you'll be reading the file twice instead of once. Why would you want to add the additional step? That's why you're using a staging table.

Luis, many thanks for your help. Now I am managed it to work.

Thanks again.