Trailer record getting inserted in the middle

Question

Hi All,

I am trying generate a csv file with header and trailer by joining two different sources. I have created two branches after joining two sources. 1) detailed records 2) generating trailer. After deriving the trailer then doing union of two branches by position. The data preview shows at the last

The trailer record is getting inserted in the middle if i create branch and derive trailer.

The above output is mocked output. But the actual file is getting generated in the same way it appeared in the mockup screenshot.

Sink setting is simple that output to single file.

Appreciate your help here.

Thanks,

Jaganath

Answer 1

Jaganath Kumar ,

Thankyou for putting effort and sharing the approach regarding issue resolution.

Glad that you figured out the way to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

Setting the partition to 'single partition' instead of 'use current partition' led to trailer record appearing at the end.

Kindly accept the answer by clicking on Accept answer button so that the solution reaches the community in case anyone is facing similar issue. Thankyou.

Answer 2

Hi Jaganath Kumar ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

As per my understanding, you are not getting expected result after performing union between two datasets. Trailer record which should appear at the end is coming in the middle of the output. Please let me know if that is not the correct understanding.

• Use surrogate key transformation in both the branches . It will assign unique incremental value corresponding to each records. For the second branch having tailer record, keep the surrogate key value as highest value.
• After union , use sort transformation on surrogate key column , it will supposedly order the records by the key column.

Hope it helps. Kindly let us know how it goes. Thanks