Data missing from output of additional columns

Highlighted
Five Stars

Data missing from output of additional columns

Hi All

I have created an additional column for a DBoutput component which gets a sequence value from the connected database.Add_Columns.jpg

The column titleid is included in both the input and output schemas.Schemsa.jpg

The joob inserts records with the correctly generated sequence number.  However, the subsequent dataflows are missing the values generated.remaining_job.jpg

 

A logfile or examination of the written data shows that for both rows the value of titleid is null.

The audit_inserts needs to be able to record the sequence generated ID's during the job. 

I thought the advanced additional columns was the way to go, but appears I am missing something. 

(It is not possible, in this case, to use a Talend generated sequence)

Any ideas why the values are missing from the pipeline flow into Audit_inserts but are written correctly to Insert_Recs?  

Eleven Stars

Re: Data missing from output of additional columns

As it is advanced column ,these columns are computed in DB engine not in DI environment , So  this value will not be passed to next flow in Design.

 

 

 

Regards
Abhishek KUMAR
Five Stars

Re: Data missing from output of additional columns

Hi Akumar,
Yes, I cam to the same conclusion.
This puts me back to the initial issue of trapping the sequence numbers generated by the underlying DB. Any suggestions? I have checked the other threads on this issue and believed advanced additional columns to be the answer ... alas no.
Employee

Re: Data missing from output of additional columns

Hi,

 

    If you are using the sequence like in advanced column, please try to commit the data before moving to next segment. Then fetch the information again (of the sequence number) using the  original candidate keys of the records.

 

   But I would think of another easier option. You can store the last modified sequence number in a control table. Then at the prejob stage, fetch the data of this value and store to a context variable.

 

   Now, for the key column, ad the Talend numerical sequence number to insert data. But instead of starting from 1, use the context variable as the starting value.

Numeric.sequence("s1",context.max_value,1)

In this way, you can maintain the sequence using DB and Talend. You can store the maximum value of sequence back to control table at the last stage of the job (either from existing result set or by querying DB based on your comfort level).

 

   This could be an easier option if you want the sequence number further in your flow.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download