Connection closed when subjob not done yet.

[rereasdev29]

Hello everyone.

I have a simple integration job from source SQL Server to destination SQL Server on different DB with TMap used for filter date from source if the date more than date on destination DB.

after the insert, the last sequence_id was iserted to context.

then i have issue when destination still on load/ still not run, the all job marked OK and goto another subjob. and the error "Invalid state, the Connection object is closed." because component "load db still on progress".

but when i Deactivate TJavaRow_1, the error is dissapear.

Any suggest to help me fix this error? because i need the last sequence_id after inserted to db.

you can look my subjob below

you can see, the component "Load destination table" on starting but the job is straight away to insert without wait.

Thank you in advance.

[Anonymous]

Can you show the whole job please? It looks like your layout may need tweaking at little. Your "Load Source table" SQL component appears to have an OnComponentOK link and a RunIf link. This seems a little unusual.

[rereasdev29]

Hello rhall,

this is complete job that i have.

 

the error come when component "Truncate Data Destination" running. that component will run if we setting some context with value != 0, when that component isn't running, job was successfully run without error or TJavaRow_1 is deactivate, "Truncate Data Destination" will run successfully with context != 0 or context == 1.

[Anonymous]

It looks like you are closing your connection far too soon. I think you need to rebuild this in a different way.

Your source and target connection components should be connected to a tJava at the very beginning. That tJava should be connected to the processing Subjob using an OnSubJobOK link. The processing should take place entirely. Then link to a "Commit" component via an OnSubJobOK link. Then link to your "Close Connection" component. At the moment your "Close Connection" component is being called after your Load source table component has errored or by the RunIf from the "Set job error" component when the RunIf logic equals true.

You should also build your jobs so you can read them like you would read a book. Top left to bottom right. The flow of your components in a single piece of processing (a SubJob) should run left to right and your SubJobs should be flowing top to bottom. This allows you (and others) to easily read your jobs.

All of the above I would advise you try and implement, but I think I have just seen your issue. Your "Close Connection" component is not firing in the above screenshot. The connection isn't closed. But you have truncated your "destination" table and it doesn't look like you have committed that. This will cause problems if you subsequently try to read from it without having committed it. Ideally I think you should try this by creating a connection with each of your components. If you do this, they will auto-commit. This will make it much easier for your to achieve what I think this flow is trying to achieve.

[rereasdev29]

I'm new on talend. if you like, could you give me example to rebuild this job because i build that job from reference my colleague.

About Trucated the "Destination" table, what i think build the job is when data fail integrated to destination, all job like Truncate Destination, data was send to destination must be canceled, so the old data is still same like before the integration.

[Anonymous]

I've just had another read of your description. What is the tJavaRow_1 doing? You say that it works when this is removed.

I'm afraid I cannot suggest how you should build this better since it is a use case that I cannot see or work on. It's a little bit like trying to paint a portrait with your eyes closed. The only recommendations that I can make are.....

1. Break the problem up into sections and complete those sections with subjobs
2. Build the job from left to right, top to bottom. This will help you and others understand what is happening.
3. Use fewer OnComponentError links and don't link different sections to the same error handling components. This is difficult to understand and can lead to an unexpected flow of data unless you are 100% sure of the timing.

If you are a Talend customer, you may have access to the Talend Academy training. If that is the case, I would recommend you take a couple of courses on Talend Studio. These will quickly get you approaching Talend jobs in a more structured way.