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?  

Highlighted
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
Highlighted
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.
Highlighted
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 :-)

2019 GARTNER 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

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog