Last Table's Data is not migrated (tDBOutput)

Highlighted
Eight Stars

Last Table's Data is not migrated (tDBOutput)

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

dbinputdbinput

 

DBOutput

DBOutputDBOutput

 

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

tbl2.png

 

2nd (last) table - No Data

tbl1.png

Highlighted
Nine Stars

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

@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

 

Highlighted
Eight Stars

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

@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

Parent-jobParent-job

test04_1(Child-job)

Child-jobChild-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

Highlighted
Nine Stars

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

@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

Highlighted
Eight Stars

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

@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 contextchild'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?

 

contextcontext

 

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 ParamsParent 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)

 

Highlighted
Nine Stars

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

@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 

Highlighted
Eight Stars

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

@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

Child JobChild 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?

 

Highlighted
Nine Stars

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

@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 

Highlighted
Eight Stars

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

@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!

 

Highlighted
Nine Stars

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

@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

Highlighted
Eight Stars

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

@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.

Highlighted
Nine Stars

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

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 ?

Highlighted
Eight Stars

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

@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!!

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 1

Learn how to do cool things with Context Variables

Blog

Talend Cloud Developer Series – Defining Metadata

This video focuses on different methods of adding metadata to a job in Talend Cloud

Watch Now

Talend Cloud Developer Series – Updating Context Variables

This video will show you how to add context parameters to a job in Talend Cloud

Watch Now