topic Re: Last Table's Data is not migrated (tDBOutput) in Talend Studio

Last Table's Data is not migrated (tDBOutput)

Anonymous — Sat, 16 Nov 2024 02:59:49 GMT

Hi,

Using the following job and settings, I was able to dynamically migrate a whole database from MSSQL to MySQL. However, I noticed that the last table's data is blank (only the table structure and column is migrated).

**The table and its schema is migrated, but no data inside the table. // I'm using a dynamic schema.

Job Design and DBInput

dbinput

DBOutput

When I tried to logged each component, I found that the last table's data is loaded and pass on from tDBInput to tMap and also from tMap to tDBOutput, as the last table's data showed up in the log.

However, when I check the actual output database, only the last table contains no data at all.

Output DB's 1st Table - Have data

2nd (last) table - No Data

Re: Last Table's Data is not migrated (tDBOutput)

nivedhitha — Fri, 13 Mar 2020 20:20:26 GMT

@crotmn ,

I have 2 suggestions for you

1. Can you try connecting to tDbInput_1 from tJava_1 on 'Iterate' link and run the job again?

Now that tDbInput is connected on 'On Component Ok', it is triggered only after all the iteration are done. But you want tne tDbInput subjob to also be iterated.

If this solution doesnt work then try the 2nd option

2. Create a subjob and move the tDbInput subjob to the child job and call this on 'Iterate' link.

Let me know if neither of these work

Re: Last Table's Data is not migrated (tDBOutput)

Anonymous — Mon, 16 Mar 2020 07:09:27 GMT

@nivedhitha

Thanks for your answer.

1. Can you try connecting to tDbInput_1 from tJava_1 on 'Iterate' link and run the job again?

I tried that but the following error comes up from tDBInput_1.

"A component that is not a subprocess start cant have link on component link."

so I tried the 2nd suggestion.

2. Create a subjob and move the tDbInput subjob to the child job and call this on 'Iterate' link.

Here's is the job design for the parent and child job.

Parent-job

test04_1(Child-job)

Child-job

It seems that the child job is not called with the current job design.

Can you help point up what I can improve?

Thank you

Re: Last Table's Data is not migrated (tDBOutput)

nivedhitha — Mon, 16 Mar 2020 10:54:05 GMT

@crotmn ,

I'm pretty sure the job is being called. It is just that the inputs(table name) the child job needs are not being passed here.

You will not be able to access tableList's global variable in the child job and hence they must be passed as context variables.

Create a context variable in the child job first say 'tableName' and then pass the value for this in the tRunJob of the parent job under 'context param'. When you try to add entries, the context variables in the child automically comes up in the drop down and assign value for it using the global variable.

And then update the tDbInput component in the child job to use the context variable rather than the global variable.

With this design, for very iteration, a different table name is passed to the child.

Let me know if it doesnt work

Re: Last Table's Data is not migrated (tDBOutput)

Anonymous — Mon, 16 Mar 2020 15:29:02 GMT

@nivedhitha

Thanks for getting back to me.

I see what you mean, but I'm not confident about context variables, so please let me confirm my understanding here.

I tried to fixed my job design as in the screenshot below and I have an error message as well.

1) Create a context variable in the child job first say 'tableName'

Child's Job

child's Job context

I bet it's this one, but I'm not sure how I should set the value.

Is this the right way?? (the actual one, I left the value blank)

or should I set this one too?

context

2) then pass the value for this in 'tRunJob' of the parent job under 'context param'.

3) When you try to add entries, the context variables in the child automatically comes up in the drop down and assign value for it using the global variable.

Parent's Job

Parent Job's Context Params

Here I select 'tableName' from the dropdown list and use global variable as the value.

4. Update the tDbInput component in the child job to use the context variable rather than the global variable.

<child's Job>

tDBInput's Table Name:

((String)globalMap.get("tDBTableList_1_CURRENT_TABLE")) change to

context.tableName

basically, change global variable to context variable.

I tried running the job with current setting and here's the error message.

Exception in thread "main" java.lang.Error: Unresolved compilation problems: 
	org.talend.job cannot be resolved to a type
	org.talend.designer cannot be resolved to a type
	org.talend.designer.components.util.mssql.MSSqlUtilFactory cannot be resolved to a type
	org.talend.logging cannot be resolved to a type
	org.talend.job.audit.JobEventAuditLoggerFactory cannot be resolved to a type
	at local_test.test04_1_0_1.test04_1.<init>(test04_1.java:162)
	at local_test.test04_0_1.test04.tDBTableList_1Process(test04.java:1032)
	at local_test.test04_0_1.test04.runJobInTOS(test04.java:1751)
	at local_test.test04_0_1.test04.main(test04.java:1538)

Re: Last Table's Data is not migrated (tDBOutput)

nivedhitha — Mon, 16 Mar 2020 15:37:09 GMT

@crotmn ,

You are passing the value right. The value for the context variable in the child job should be empty as it get its value dynamically from the parent job.

Also can you make a connection between tDbConnection_2 and tDbInput_1 in the child job? As there is no preJob component , the connection and the input components will be executed at the same time.

Where are you getting the target table name from? If that is also from the table in parent job, then it has to be passed to the child job too

Re: Last Table's Data is not migrated (tDBOutput)

Anonymous — Mon, 16 Mar 2020 16:49:44 GMT

@nivedhitha

Thanks for your prompt response.

"Also can you make a connection between tDbConnection_2 and tDbInput_1 in the child job? As there is no preJob component , the connection and the input components will be executed at the same time."

> For the connection you mentioned here, is 'OnSubJobOk' good for this?

or should I add a preJob component? I had it in the parent job already, so I'm not sure if I should use it again here.

Child Job

"Where are you getting the target table name from? If that is also from the table in parent job, then it has to be passed to the child job too "

> You mean in the child job right? If yes, I use a context variable you mentioned in the earlier thread as a table name. If there's any changes needed please let me know.

By the way, I set up a connection component (input&output) in both parent and child job, is it needed for both job? or having in it either job would work fine too?

Re: Last Table's Data is not migrated (tDBOutput)

nivedhitha — Mon, 16 Mar 2020 17:05:53 GMT

@crotmn ,

For your first question, Yeah 'On Subjob Ok' should be fine.

> You mean in the child job right? If yes, I use a context variable you mentioned in the earlier thread as a table name. If there's any changes needed please let me know.

- Yeah i see you using in the tDbInput component. So it is the source table. Is the table name in the tDbOutput component dynamic too? If so you will need another context variable similar to the source table and pass value for it as well.

> By the way, I set up a connection component (input&output) in both parent and child job, is it needed for both job? or having in it either job would work fine too?

- It doesnt matter if you create them in both the parent and the child. But there is an option to reuse the connection created in parent job in the child job.

If the current design works, leave it like that.

If you still have problems getting it to work, please export your job and attach it here or DM it to me

Re: Last Table's Data is not migrated (tDBOutput)

Anonymous — Mon, 16 Mar 2020 17:15:28 GMT

@nivedhitha

Thanks for getting to back to me.

Yeah i see you using in the tDbInput component. So it is the source table. Is the table name in the tDbOutput component dynamic too? If so you will need another context variable similar to the source table and pass value for it as well.

> Yes, the table name in tDBOutput is 'context.tableName'. Can I use the same one with tDBInput??

With the current job design, the same error still occurred.

Exception in thread "main" java.lang.Error: Unresolved compilation problems: 
	org.talend.job cannot be resolved to a type
	org.talend.designer cannot be resolved to a type
	org.talend.designer.components.util.mssql.MSSqlUtilFactory cannot be resolved to a type
	org.talend.logging cannot be resolved to a type
	org.talend.job.audit.JobContextBuilder cannot be resolved to a type
	JobAuditLogger cannot be resolved to a type
	org.talend.job.audit.JobEventAuditLoggerFactory cannot be resolved to a type
	at local_test.test04_1_0_1.child_job.<init>(child_job.java:162)
	at local_test.test04_0_1.parent_job.tDBTableList_1Process(parent_job.java:1032)
	at local_test.test04_0_1.parent_job.runJobInTOS(parent_job.java:1751)
	at local_test.test04_0_1.parent_job.main(parent_job.java:1538)

PS. DbConnection2 is actually for output, and DbConnection1 is for input. Isn't it better to connect DbConnection1 with DBInput1? and vice versa?

I feel like there's something needed to be fixed in the child job.

Thanks!

Re: Last Table's Data is not migrated (tDBOutput)

nivedhitha — Mon, 16 Mar 2020 18:39:27 GMT

@crotmn , if the input and output tables are the same the its fine to have one context and use it both in DbInput and DbOutput.

Its okay if one is an input connection and the other is output. You can leave the design unchanged as both the connection has to be made in order for the job to execute. That is why the connections are made before the actual processing takes place.

To see what the error could be, can you run just the child job directly? It says compilation error so it will show you the component with error.

Also it would be better if you can attach an exported copy of the job with all your credentials removed

Re: Last Table's Data is not migrated (tDBOutput)

Anonymous — Tue, 17 Mar 2020 00:16:43 GMT

@nivedhitha

Thank you for your reply.

Here's the error for childjob

Exception in component tDBInput_1 (test04_1)
com.microsoft.sqlserver.jdbc.SQLServerException: An object or column name is missing or empty. 
For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names.


Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:885)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:778)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:677)
	at local_test.test04_1_0_1.test04_1.tDBInput_1Process(test04_1.java:1523)
	at local_test.test04_1_0_1.test04_1.tDBConnection_2Process(test04_1.java:807)
	at local_test.test04_1_0_1.test04_1.tDBConnection_1Process(test04_1.java:586)
	at local_test.test04_1_0_1.test04_1.runJobInTOS(test04_1.java:2935)
	at local_test.test04_1_0_1.test04_1.main(test04_1.java:2730)

Here I have exported the project, please see if you can open it.

test04_1.zip

Re: Last Table's Data is not migrated (tDBOutput)

nivedhitha — Fri, 20 Mar 2020 03:04:34 GMT

Hey @crotmn , I tried importing your project but I'm not able to. Can you please export just this one job and not the project ?

Re: Last Table's Data is not migrated (tDBOutput)

Anonymous — Mon, 23 Mar 2020 01:45:43 GMT

@nivedhitha

Thanks for getting back.

Here I only export the parent and child jobs.

***Update

I tried running the job again and found that there's no error anymore.

However, I realize that only the table and schema is imported to the output database but not the data.

Though the data shows up in the run job log. (I logged DBOutput in the childjob)

Please let me know how it goes.

Thanks alot!!

test_parentjob.zip