[resolved] Get a File Load Details after success or fail load

Five Stars

[resolved] Get a File Load Details after success or fail load

Hi
i have created job which takes input from database like file name, path, table name etc. so my job look like below.
Database
|
iterate
|
tFileExist---if----tFileInputDelimited----main---tFlowMeter---tMSSQLOutput
|
Reject
|
tFlowMeter
|
tFileOutputDelimited
so my problem is to stored details of each file load in a separate database table. but i am not able to sort this issue. i tried to put the final output in after on sub job Ok event of tFileInputDelimited but it was not working. then took if connector of tMSSQLOutput with error message == success condition but it was not working.
so can one suggest me how accomplish this task.
Note. i need to put following details in my final table
1. Number of row rejected
2. input File Name
3. Number of rows loaded to SQL
4. Total rows from source file
5. Job Details
and many more but these are the important.

Accepted Solutions
Five Stars

Re: [resolved] Get a File Load Details after success or fail load

Hi
after long trial and error, i got the solution how to get loaded and recorded count. using context variables and Tjavarow component.
please see the following diagram which helps me.
tSQlRow----->T Flow To Iterate------> t Java Row-- on component ok----> T File Input ------tjavarow----Tsqloutput
| \
| Rejected rows
on sub job ok \
| tjava row
t SQl Row \
tFileOutput
we have created variables to store the records count for rejected and loaded records. first java row component is use to intilize variables with zero. then 2 t java tow is use for storing loaded records in SQL table. 3 variable is use to store rejected record count. once the job ok with load we are updating records count in SQL table using t SQL row. this is working for each file we loaded in a single table.
thanks for help and suggestions.

All Replies
One Star

Re: [resolved] Get a File Load Details after success or fail load

Hi
According to your requirement in this topic, I think you create this job in a wrong way.
But I can't do this for you because of limited information.
What's the structure of DB table?
Why do you put "Reject" with tFileInputDelmited?
I feel puzzled.
Regards,
Pedro
Five Stars

Re: [resolved] Get a File Load Details after success or fail load

Hi Pedro
it will help full to me if you give me right way to do this job. i just want to load the delimited or any type of file, in a separate db table. my input is table where i have created a inventory for all the files, we received from client. and accordingly we are loading files to db table. in case of schema issue or any other reason data may rejected, so we are dumping those rejected rows into delimited file.
my db table has following schema.
id int,
fileName varchar,
TableName varchar,
LoadedRecordCount numeric,
RejectedRecordCount numeric,
Status varchar,
RejectedRowFileLocation varchar,
LoadDate datetime,
TotalRecords numeric.


i think this information helps you. please let me know, if you need any thing else.
One Star

Re: [resolved] Get a File Load Details after success or fail load

Hi
It will be better to talk with you by skype because the job logic is complex. But you are not online...
Regards,
Pedro
Five Stars

Re: [resolved] Get a File Load Details after success or fail load

Hi Pedro,
i am still looking for solution, but i am having a question regarding, "Monitor This connection link" associated with every connector(Connection). so how can use monitor connection properties to get a count of rejected and loaded count.
i have seen for many out put component (tfileOutPutDelimited, tMSSqlOutput) don`t have "On sub Job ok" action?
One Star

Re: [resolved] Get a File Load Details after success or fail load

Hi,
The "Monitor this connection link" checkbox on the rows is used to collect information you can use later using a tFlowMeterCatcher. This makes using the tFlowMeter not necessary any more.
About the "On Subjob Ok": this is a connection that can only be made from the first component in a "subjob" (this is outlined with a blue background on the canvas, the first component having a green background).
I think it should be possible to get all required values from inside the job, for example the reject row will have a counter which you could use, and so does the main flow from you tMap.
Hope this helps.
Regards,
Arno
Five Stars

Re: [resolved] Get a File Load Details after success or fail load

Hi
after long trial and error, i got the solution how to get loaded and recorded count. using context variables and Tjavarow component.
please see the following diagram which helps me.
tSQlRow----->T Flow To Iterate------> t Java Row-- on component ok----> T File Input ------tjavarow----Tsqloutput
| \
| Rejected rows
on sub job ok \
| tjava row
t SQl Row \
tFileOutput
we have created variables to store the records count for rejected and loaded records. first java row component is use to intilize variables with zero. then 2 t java tow is use for storing loaded records in SQL table. 3 variable is use to store rejected record count. once the job ok with load we are updating records count in SQL table using t SQL row. this is working for each file we loaded in a single table.
thanks for help and suggestions.