Hi All,

I have a data pump task which pumps data from a source to destination,I'd
like to check every single field in each row and if any of them is null,I'd
like to update the last field in the row and skip other field processing in
the row and move to the next row.

How Can I do that?

Thanks

I think this has been answered in your earlier posts. Is it still not
working for you?

"RayAll" <RayAll (AT) microsft (DOT) com> wrote

hi Simon,
No it's not working in that way.

Look ,here is the fields in each row:

Field1 Field2 Field3 Field4 Error_Code

let's say there is a validation error in Field2 ,I'd like to write my own
code in Error_Code ** And not process Field3 and Field4 at all** ,I'd like
to skip processing them ,I used this script but it still dosen't skip the
processing of each field ,It DOSE NOT write the row into the destination but
it still processes all other fields.


' Copy each source column to the destination column
Function Main()
IF Validation THEN

DTSDestination("RECORD_TYPE") = DTSSource("Col001")

'Column 4 is checked
DTSDestination("COLUMN_LEVEL") =4

'Successfull Validation
DTSDestination("ERROR_STATUS") = 3
Main = DTSTransformStat_OK

ELSE
'Terminate further processing of this row,for non-error reasons
DTSDestination("ERROR_STATUS") = 1
Main =DTSTransformStat_SkipRow

END IF

End Function

' Validate each source column
Function Validation()
IF DTSSource("Col001") <> "C1" THEN
Validation = False
ELSE
Validation = True
END IF
End Function



Thanks for yuor reply


"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote

So are you saying that you want a row in the table to have no information in
it except an error code in the last column if any business rules for that
row fail?

"RayAll" <RayAll (AT) microsft (DOT) com> wrote

You know what,I think I have to implement what I want,because you are right
if I skip processing other firleds,information is not written too,so I have
to manully define a global variable
at the first field set it to "True" and if any **Validation** error occures
,I set it to **False** and in all frields from 2 to the end ,I check if it's
false I don't do the validation

Dose that make sence?

Thanks Simon
"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote

Am I correct in understanding that you would like to log errors for rows
that fail business rules you've defined?


"RayAll" <RayAll (AT) microsft (DOT) com> wrote

YES,YOU ARE RIGHT AND THAT LOGGING IS ONE FIELD OF EACH RECORD.

Let's not call it biz rules ,just consider a simple length check ,I want to
say for instance the 4th field's length is invalid and I don't want to
check the length on the others,because one is enough:-)

this error handling is record basis,because I want to create a report based
on that later.

Thanks
"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote

So why not go ahead and capture the full record anyway, don't skip it,
insert it as usual, still update your errorcode column with the reason why
it failed, then when you're reporting on bad data, you'll have the detail of
the record to manually review if questioned by a user whe viewing the
report.
if you don't capture the full record then you don't have the evidence needed
to prove your report, and properly evaluate test cases.

"RayAll" <RayAll (AT) microsft (DOT) com> wrote

it's exactly what I came up with.But I don't want to validate other fields
when there is a problem with one field.We need to report only one failure at
once .I have to insert all the rows but I'd like to update my error_field
once.The usage of the global variable is for that too.

What do you think about this?
Thanks
"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote

OK, now we're on the same page. I understand what you are saying now.

So, in your activeX transformation you can assign all the transformations
IE
SourceColumn1 = DestinationColumn1
SourceColumn2 = DestinationColumn2
SourceColumn3 = DestinationColumn3
SourceColumn4 = DestinationColumn4
SourceColumn5 = DestinationColumn5
SourceColumn6 = DestinationColumn6
etc, etc...

Then, run checks (before or after the transformations, it's up to you) on
the columns to see if any break a rule

IF SourceColumn1 <BreaksRule> THEN
DestionationErrorCodeColumn = Column1ErrorCode
ELSEIF SourceColumn2 <BreaksRule> THEN
DestionationErrorCodeColumn = Column2ErrorCode
ELSEIF SourceColumn3 <BreaksRule> THEN
DestionationErrorCodeColumn = Column3ErrorCode
ELSEIF SourceColumn4 <BreaksRule> THEN
DestionationErrorCodeColumn = Column4ErrorCode
ELSEIF SourceColumn5 <BreaksRule> THEN
DestionationErrorCodeColumn = Column5ErrorCode
ELSEIF SourceColumn6 <BreaksRule> THEN
DestionationErrorCodeColumn = Column6ErrorCode
etc, etc...
END IF


"RayAll" <RayAll (AT) microsft (DOT) com> wrote