Five Stars

Problems Using tFileOutputMSPositional

Hello,

 

I am unable to find much information on the use of the tFileOutputMSPositional component and I am stuck on a project.

I have 2 delimited files.  The parent contains basic information on a traffic accident and the child contains the vehicles involved.  They both have a field named 'LocalReportNumber' which acts as the primary key in the parent file and a foreign key in the child file.  When I execute the job, I expect to get 3 rows.   One row for the parent and a row for each of the two vehicles involved.  I get only the parent row, every time.   I get no errors.   When I run the job, it shows that 2 rows were read from the child row and a traces debug shows info read from the child row.   Very frustrating.   Below is a screenshot of my basic job setup.   I do wonder about the 'Pattern' column.  I assume that should be the length of each field in the positional file.  Is that correct?  Like I said, I am having problems finding information or samples for this component.  Any help is much appreciated.

 

Capture.PNG

 

  • Data Integration
1 ACCEPTED SOLUTION

Accepted Solutions
Five Stars

Re: Problems Using tFileOutputMSPositional

I figured it out.  It doesn't entirely make sense, but I was able to get all 3 rows to show by changing the 'Key Column' of row2 to 'LocalReportNumber'.   The thing is, LocalReportNumber is a foreign key for row2 and the unique key is 'UnitNumber'.   However, changing that made it work and allowed me to add a few more rows I needed.  My take away was that the parent key column and the key column for a row need to be the same.  Doesn't make much sense or adhere to the documentation I found, but at least it worked in my case.   I now get 8 rows and they are in the order I expect.  My text file comes out as follows

 

1 - main accident data (schema: row1)

2 - Unit involved #1 data (schema: row2)

3 - Driver of unit involved #1 data (schema: row3)

4 - Unit involved #2 data (schema: row2)

5-  Driver of unit involved #2 data(schema: row3)

6-  Occupant #1 of unit involved #2 data(schema: row3)

7-  Occupant #2 of unit involved #2 data(schema: row3)

8-  Witness to accident data(schema: row4)

 

Capture.PNG

3 REPLIES
Seven Stars TRF
Seven Stars

Re: Problems Using tFileOutputMSPositional

Hi,

As we can see in the screen capture, the parent row seems to have a strange schema name (I suppose tFileOutputMSPositional_1 but it should row1.

Can you try to change this?


TRF
Five Stars

Re: Problems Using tFileOutputMSPositional

Yes.  thanks for taking a look at this.  I did notice that oddity.  If I click on that value it turns to 'row1', but automatically reverts to the name of the output component.

 

I just deleted the output component and re-added it.  Now it does show row1.  Once again, the job runs without error, but my output file only returns the data for row1.   Seems like it must be something simple I'm missing.

Five Stars

Re: Problems Using tFileOutputMSPositional

I figured it out.  It doesn't entirely make sense, but I was able to get all 3 rows to show by changing the 'Key Column' of row2 to 'LocalReportNumber'.   The thing is, LocalReportNumber is a foreign key for row2 and the unique key is 'UnitNumber'.   However, changing that made it work and allowed me to add a few more rows I needed.  My take away was that the parent key column and the key column for a row need to be the same.  Doesn't make much sense or adhere to the documentation I found, but at least it worked in my case.   I now get 8 rows and they are in the order I expect.  My text file comes out as follows

 

1 - main accident data (schema: row1)

2 - Unit involved #1 data (schema: row2)

3 - Driver of unit involved #1 data (schema: row3)

4 - Unit involved #2 data (schema: row2)

5-  Driver of unit involved #2 data(schema: row3)

6-  Occupant #1 of unit involved #2 data(schema: row3)

7-  Occupant #2 of unit involved #2 data(schema: row3)

8-  Witness to accident data(schema: row4)

 

Capture.PNG