How to capture # of rows from source,inserted,updated,deleted?

[rp2018]

Need to capture following information to a Sql Server table for each of my Talend jobs.

Have a Talend job whose source is Sql Server db and destination is also a Sql Server db.  Using tmap to compare then doing inserts,updates and deletes  How can I capture what is my jobname,jobstarttime,jobendtime,source qty,insert qty,update qty and delete qty?  

This is my job design:

                        tMSSqlInput(Lookup)

                                  |

tMSSqlinput-->        tMap--------------tMSSqlOutput(Insert new rows)                                                          

                                                         tMSSqlOutput(Update rows)                                                                                                                             tMSSqlOutput(Delete rows)

[TRF]

Start date is not directly available as a variable but you can set a global after the tPreJob with the following TalendDate.getDate("yyyy-MM-dd HH:mm:ss").
Same for end date after a tPostJob.
For the rest, see global variables created for each component such as NB_LINE_INSERTED, NB_LINE_UPDATED, NB_LINE_DELETED and NB_LINE.
You get the value after the component has finished to process the rows with the following syntax:
((Integer) globalMap.get(" tMSSqlOutput_1_NB_LINE_INSERTED"))
Except for NB_LINE, if the row count exceed 9999, the returned value is 0. In this case you need to store the concerned rows into a tHashOutput and get the number of rows from tHashOutput_1_NB_LINE.
To get the job name, just refer to the standard variable jobname where you want.

[Anonymous · Accepted solution]

In all DI jobs, the Start time, End time and duration  can also be derived using  tChronometerStart and tChronometerStop component. 

For Big data jobs, please follow the suggestions given by TRF.

Here is the snippet of a DI job for your convenience. 

Content of tJava:

long ST = ((Long)globalMap.get("tChronometerStart_1_STARTTIME"));
long ET = ((Long)globalMap.get("tChronometerStop_1_STOPTIME"));

Date Start_Time_1=new Date(ST);
Date End_Time_1=new Date(ET);

SimpleDateFormat df1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String Start_Time = df1.format(Start_Time_1);
String End_Time = df1.format(End_Time_1);

System.out.println("\n ++++++++++++++++++++++++ Job Summary ++++++++++++++++++++++++ \n");

System.out.println("\n Job Name : " + jobName);

System.out.println("\n Start Time : " + Start_Time);

System.out.println("\n End Time : " + End_Time);

System.out.println("\n Job Duration : " + ((Long)globalMap.get("tChronometerStop_1_DURATION")) + " milliseconds");

System.out.println("\n Input Record Count : " + ((Integer)globalMap.get("tFileInputDelimited_1_NB_LINE")));

System.out.println("\n ++++++++++++++++++++++++ Job Summary End ++++++++++++++++++++++++ \n");

Output:

Hope this helps!!!!

Regards,

Ragu

[rp2018]

This is exactly what I was looking for.  Many Thanks!!

[rp2018]

How do you pass the variables information to the next component from tJava?  I am able to see all of the results in the run window, but unable to capture it to tMSSqloutput to insert rows to a log table.

[Anonymous]

Start date and end dates are derived along with the formats. so assign it to a context variable with string data type. use those context variables in tMap and connect it to tMSSqloutput. 

Duration, no of records are from global map, so you can use it directly in tMap.

[rp2018]

When I link from tJava to tMap, I'm not getting any fields.  Please advise.