You are not logged in.
I have a project in which I have to do some renumbering of some ID fields.
I read from an Access DB and pump the old ID's in a pipe delimited file...here is a sample of that data...
In a separate subjob, I am looking up the first number to return the second number using a tMap component.
For some reason that I have not been able to determine, when it reaches this record...
101036304|7253|101036305 in the tInputDelimited
It does not do the look up, and it does not give an error. But in my results file, it leaves the result of that lookup empty, while all the previous and subsequent records are fine.
I attached a tLogRow component between the tInputDelimited and the tMap and it gives me Column(s) missing when it hits that record.
can anybody explain what could possibly be happening here?
Yes the lookup is definately there. If it wasn't there, then the tLog would never get the request for the line
101036304|7253|101036305 and respond with 'Column(s) missing'...
The job is producing correct results.
So the source gives 101036304 as the lookup value and for every record except that one the result file looks like this...
2345|some other data|some more other data|2345|and some other data
But for the record in question for which the tLog returns 'Column(s) missing' -- which is clearly a bug becuase all the lookup records have three values V1|V2|V3 --
the result file ends up having...
|some other data|some more other data||and some other data
the tFileDelimited file doesn't have any strange invisible control characters...it is simply a text file produce by a previous job that has V1|V2|V3
Something that is broken somehow is choking when it gets to the record in question.
I have tried changing all the types to integer/long/or string and in all cases the tLog says 'Column(s) missing' when it hits that record for which all columns are present.
It sounds like the ID's are not matching in the Join. See if you can isolate the values in the lookup file and make a file of JUST those values.
Once you have a file of "broken lookups" it should be a little easier to find the problem. Remember you can look at the generated code to see exactly what is happening. Also, tPerlRow/tJavaRow can be used to insert debug prints to get an idea of the values during the run.
That would not explain the tLogRow component sending the message 'Column(s) missing'.
In fact, the existance of the 'Column(s) missing' message in the tLogRow is evidence that it found the lookup value,
and, since the 'Column(s) missing' is being provided as a result of the lookup, on a record that consists of the same number of delimited fields as the previous and next record in the tFileDelimited file, I think something is bugged in tFileInputDelimited.
The tMap is using an inner join with reject output on the lookup row, so the existance of the output record is also evidence that it found the lookup value. It just for some reason is perceiving the 101036304|7253|101036305 as being less than 3 fields, even though it perceives the previous and next records, that have the same number of fields, as having enough.
I didn't have time today for any troubleshooting but will tomorrow.
I mainly posted here to see if anybody had ever encountered anything quite this strange.
I did take a glance this morning at the generated code, but not extensively.
My next move is to replace the tFileDelimited with tFileOutputExcel (for it's creation) and tFileInputExcel (for the lookup)
However, I think the reason I chose the tFileOUTINDelimited in the first place was because the tExcelOutputDelimited doesn't do Excel 2007 and I have more than 65k rows of data.
I would use TOS 4.2 but it has a broken tJavaFlex component.
I would use TOS 4.2 but it has a broken tJavaFlex component.
This bug have been fixed, if you are using TIS, you can open a support ticket and ask for a patch.
OK I copied the offending subjob to it's own job and replaced all of my context variables with actual links to the file name/locations and ran it and the error did not occur.
Anybody have any reasonable explanations for this?
OK I removed the record entirely, and I got the error on the very next record...
Ths subjob that creates the map creates the map by using the old id for the first field, a numeric.sequence for the second field, and another old id for the third field which is not referenced in the subjob I am having problems with.
So it creates a pipe delimited text file of the form OLDID|NEWID|FAMILYNUMBER and includes a header.
The errant subjob appears to fail at record 7253, which happens also to be the NEW ID number since the sequence starts at 1
The tMap component gets data that has the old id, and uses the old id to lookup the new id.
I have a tLog component between the tFileInputDelimited and the tMap, and it always fails on record 7253 regardless of the lookup id.
The errant subjob gets it's source from an Access table that has multiple entries (potentially) for each record in the map.
So the result after all lookups is a file that will have the following format...
But anyway, I have confirmed, this happes on the 7253rd record everytime, regardless of the data...
Any clues as to what might be happening are very appreciated...Thanks in advance.
OK I figured out the problem. The file was not finished being written by the time the next subjob referenced it in a lookup. This is because OnComponentOK trigger is not OnComponentFinishedWritingFile trigger.
I have attempted to attach an image of the job in question. As you can see, I hope, there are several subjobs connected by OnComponentOK triggers.
I see two solutions. One is to make all of these subjobs into their own job and use a main job to call them with tRunJobs... i am loath to do this.
Another option is the answer to this question...
Is there a way to ensure the completion of a file write before referencing the file or a way of ensuring all of the outputs from a given subjob (not just one as with the OnComponentOK trigger) are finished being created before another subjob is triggered???
Last edited by chrishansen (2010-08-17 22:10:11)