ActiveX Transformation Script, check the whole record

[m4pv]

Hello.
Is it possible to check count of field separators in ActiveX Transformation Script?
I have pipe delimited file that I need to load in a sql table. The problem is that if some row in the middle of the file has less pipes, file is still loaded but data goes to the wrong destination columns:
File:
Header1|Header2|Header3
A|B|C
A|C
Table:
Field1 Field2 Field3
A B C
A C null --> wrong result

My thought was to check count of pipes in each row during transformation in ActiveX script, but i cannot find the object that would contain the whole record from the file.
I really don't want to open file on front end app before DTS run and check it's formatting...

Thanks

 

[AlexCuse]

I don't think you can do this mid-transform.

You could pretty easily write a script to do pre-processing and delete the bad rows, and write to another file.

This will get you the count of pipes (OTTOMH)

pipeCnt = Len(strRow) - Len(Replace(strRow, "|", ""))

Hope this helps,

Alex


[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page