Validating file using without script task

Question

Validating file using without script task

naresh0407 93367

SSC-Addicted

Points: 479
More actions
June 15, 2012 at 10:21 am

#276410

Hi All,
I am loading txt file data into database.I am validating file using script task and checking each row and inserting into database.If i get error i am sending mail of that row number and reason.
Is there any other way that i can valdate that file without using script task..i.e with other transformations
like LookUp and data conversion.
How to validate each row and if i get error i should catch that row number and cause of error.

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

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

sqlbi.vvamsi Ten Centuries Points: 1194 More actions · Answer 1

what is that validation you are using? you can use derived column transformation to perform some validations like length of column, likewise.. More info can help

naresh0407 93367 SSC-Addicted Points: 479 More actions · Answer 2

In Script task i am using C# to validate text file.I have 3 rows validating all the rows using C# in scriptask and wrting Logfile and if i get error i am sending mail.

Can i use any other transformation to do this task.

In Footer i have total no datarows count and at the sametime i have 3 datarows and this should match with datarows count..

1st row is header...rows with no 2 is datarow and row with number 3 is footer.Now footer row count is 4 and no of details row is 3.Mismatch so i should send mail or write logfile without using scripttask

1 date

2 xyz 04042012

2 abc 04032012

2 nnn 33333333

3 4 date

FileReader=New streamreader(LocalFileName)

Newline=FileReader.readline

If Left(NewLine,3)==1

{

Datevlue=XXX;

}

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

I would recommend looking into the Conditional Split Transformation to help you parse the file and route each file-line-type onto a different output stream.

Then, when loading your data-lines into a table using an OLE DB Destination you can set it up to send rows that fail import (i.e. rows that end up on the Error Output stream) to an "error table" that consists of a single wide column that can hold the entire data line.

How to: Configure an Error Output in a Data Flow Component

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

sqlbi.vvamsi Ten Centuries Points: 1194 More actions · Answer 4

you can use script component instead. I think that is next better option to script task based on what you said.