Execute Child Job from Parent Job (Job order issue)

Highlighted
Eight Stars

Execute Child Job from Parent Job (Job order issue)

Hi,

 

I have two jobs that 1) copy tables to output database and 2) inject PK constraint to output database.

I want to run the whole parent job (copy DB) and then run child job (inject PK constraint to DB)

 

With the current job design, the child jobs (PK constraint injection) runs immediately after the first table is created in the parent job.

Without all the corresponding tables created in advance, the job freezes.

 

PS. Initially I wanted to triggered tRunJob component with OnSubJobOk, but there's only OnComponentOk.

PS2. When executed separately, both job works fine. Also it works fine when I first tested it with a DB with two tables.

 

Is there a way to fixed this? Jobs file attached

 

Parent Job

runjob1.png

 

 

Child Job

runjob2.png

 

Error Log

 

Starting job Migration_DB_Parent_Job at 19:05 24/03/2020.
[statistics] connecting to socket on port 3864
[statistics] connected
Table Name = DOM_AIR_MIN_PRICELIST
dbo.DOM_AIR_MIN_PRICELIST|Table|Primary key|PK_DOM_AIR_MIN_PRICELIST|start_point_cd, end_point_cd, airline_cd, tour_kind, yyyymm
dbo.DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS|Table|Primary key|PK_DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS|yyyymm, mem_id, tour_id
dbo.DOM_AIR_SEARCH_ONEWAY_TICKETS|Table|Primary key|PK_DOM_AIR_SEARCH_ONEWAY_TICKETS|mem_id, tour_id
dbo.DOM_AIR_SEARCH_ROUNDTRIP_TICKETS|Table|Primary key|PK_DOM_AIR_SEARCH_ROUNDTRIP_TICKETS|mem_id, tour_id
dbo.DOM_AIRLINE|Table|Primary key|PK_DOM_AIRLINE|airline_cd
dbo.premium_bid_for_dair|Table|Primary key|PK_premium_bid_for_dair|id
dbo.premium_entry_mem|Table|Primary key|PK_premium_entry_mem|mem_id, product_type
dbo.tbl_site_mem|Table|Primary key|PK_tbl_site_mem|site_id, mem_id
dbo.tbl_site_mem_agent|Table|Primary key|PK_tbl_site_mem_agent|site_id, mem_id
dbo.DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS|ALTER TABLE dbo.DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS ADD CONSTRAINT PK_DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS PRIMARY KEY (yyyymm, mem_id, tour_id);
Table Name = dbo.DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS 
 AND the Query is = ALTER TABLE dbo.DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS ADD CONSTRAINT PK_DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS PRIMARY KEY (yyyymm, mem_id, tour_id);


Job Migration_DB_Parent_Job ended at 19:06 24/03/2020. [Exit code  = 1]
Table 'dbo.dom_air_monthly_search_oneway_tickets' doesn't existTable 'dbo.tbl_site_mem' doesn't existTable 'dbo.dom_tour_for_da_roundtrip_search' doesn't existTable 'dbo.dom_air_monthly_search_roundtrip_tickets' doesn't existTable 'dbo.dom_air_search_oneway_tickets' doesn't existTable 'dbo.premium_bid_for_dair' doesn't existTable 'dbo.tbl_site_mem_agent' doesn't existTable 'dbo.dom_air_pl_monthly' doesn't existTable 'dbo.dom_airline0' doesn't exist
[ERROR]: local_test.primary_key_0_1.Primary_Key - tDBRow_1 - Table 'dbo.dom_air_monthly_search_oneway_tickets' doesn't exist
dbo.tbl_site_mem|ALTER TABLE dbo.tbl_site_mem ADD CONSTRAINT PK_tbl_site_mem PRIMARY KEY (site_id, mem_id);
Table Name = dbo.tbl_site_mem 
 AND the Query is = ALTER TABLE dbo.tbl_site_mem ADD CONSTRAINT PK_tbl_site_mem PRIMARY KEY (site_id, mem_id);

[ERROR]: local_test.primary_key_0_1.Primary_Key - tDBRow_1 - Table 'dbo.tbl_site_mem' doesn't exist
dbo.DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH|ALTER TABLE dbo.DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH ADD CONSTRAINT PK_DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH PRIMARY KEY (mem_id, tour_id, money_start_date, money_end_date);
Table Name = dbo.DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH 
 AND the Query is = ALTER TABLE dbo.DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH ADD CONSTRAINT PK_DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH PRIMARY KEY (mem_id, tour_id, money_start_date, money_end_date);

[ERROR]: local_test.primary_key_0_1.Primary_Key - tDBRow_1 - Table 'dbo.dom_tour_for_da_roundtrip_search' doesn't exist
dbo.DOM_AIR_MONTHLY_SEARCH_ROUNDTRIP_TICKETS|ALTER TABLE dbo.DOM_AIR_MONTHLY_SEARCH_ROUNDTRIP_TICKETS ADD CONSTRAINT PK_WORK_DOM_AIR_MONTHLY_SEARCH_ROUNDTRIP_TICKETS PRIMARY KEY (yyyymm, mem_id, tour_id);
Table Name = dbo.DOM_AIR_MONTHLY_SEARCH_ROUNDTRIP_TICKETS 
 AND the Query is = ALTER TABLE dbo.DOM_AIR_MONTHLY_SEARCH_ROUNDTRIP_TICKETS ADD CONSTRAINT PK_WORK_DOM_AIR_MONTHLY_SEARCH_ROUNDTRIP_TICKETS PRIMARY KEY (yyyymm, mem_id, tour_id);

......

 

However when I change target db to the one with two tables, it worked,, though there are error message.

Here's the log

 

[statistics] connected
Table Name = tbl_inq
traveltour.tbl_inq|Table|Primary key|PK_tbl_inq|mem_id, inq_id
traveltour.tbl_mem|Table|Primary key|PK_tbl_mem|mem_id
traveltour.tbl_mem|ALTER TABLE traveltour.tbl_mem ADD CONSTRAINT PK_tbl_mem PRIMARY KEY (mem_id);
Table Name = traveltour.tbl_mem 
 AND the Query is = ALTER TABLE traveltour.tbl_mem ADD CONSTRAINT PK_tbl_mem PRIMARY KEY (mem_id);

Table 'traveltour.tbl_mem' doesn't existMultiple primary key defined
[ERROR]: local_test.primary_key_0_1.Primary_Key - tDBRow_1 - Table 'traveltour.tbl_mem' doesn't exist
traveltour.tbl_inq|ALTER TABLE traveltour.tbl_inq ADD CONSTRAINT PK_tbl_inq PRIMARY KEY (mem_id, inq_id);
Table Name = traveltour.tbl_inq 
 AND the Query is = ALTER TABLE traveltour.tbl_inq ADD CONSTRAINT PK_tbl_inq PRIMARY KEY (mem_id, inq_id);

Table Name = tbl_mem
traveltour.tbl_inq|Table|Primary key|PK_tbl_inq|mem_id, inq_id
traveltour.tbl_mem|Table|Primary key|PK_tbl_mem|mem_id
traveltour.tbl_mem|ALTER TABLE traveltour.tbl_mem ADD CONSTRAINT PK_tbl_mem PRIMARY KEY (mem_id);
Table Name = traveltour.tbl_mem 
 AND the Query is = ALTER TABLE traveltour.tbl_mem ADD CONSTRAINT PK_tbl_mem PRIMARY KEY (mem_id);

traveltour.tbl_inq|ALTER TABLE traveltour.tbl_inq ADD CONSTRAINT PK_tbl_inq PRIMARY KEY (mem_id, inq_id);
Table Name = traveltour.tbl_inq 
 AND the Query is = ALTER TABLE traveltour.tbl_inq ADD CONSTRAINT PK_tbl_inq PRIMARY KEY (mem_id, inq_id);

[ERROR]: local_test.primary_key_0_1.Primary_Key - tDBRow_1 - Multiple primary key defined
[statistics] disconnected

 

Highlighted
Moderator

Re: Execute Child Job from Parent Job (Job order issue)

If you want to run the subjob using subjob OK then take on subjob OK from tdbinput.
Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Eight Stars

Re: Execute Child Job from Parent Job (Job order issue)

@ manodwhb

But the thing is that I wanted get child job running after parent job is done (tables copied from tDBInput to tDBOutput). so I thought of using a trigger on tDBOutput,,,
since the child job will need tables in parent's job tDBOutput..
Highlighted
Moderator

Re: Execute Child Job from Parent Job (Job order issue)

You can use the trigger of on subjob OK from tdbinputand connect to your child job.
Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Eight Stars

Re: Execute Child Job from Parent Job (Job order issue)

 

@manodwhb 

 

I did as suggested but still the same result ....

runjob3.png

Highlighted
Moderator

Re: Execute Child Job from Parent Job (Job order issue)

As per the error your table is not created for the which you're trying to do next operation.
Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Seventeen Stars

Re: Execute Child Job from Parent Job (Job order issue)

OnSubjobOk is only available on the first component of the sub job (the blue rectangle).

OnComponentOk is available on every component and means you trigger at the end of the flow especially at the end for the particular component.

Now it depends when you want to start your child job. Once per record or at the end of the flow. In this case why not trígger the child job just before you trigger your commit.

  

Highlighted
Eight Stars

Re: Execute Child Job from Parent Job (Job order issue)

@manodwhb
What I mean is that I use subjob OK from tdbinput as suggested and it didn't work.
Is there way to finish runing the whole parent job before passing the operation to child job?
Highlighted
Eight Stars

Re: Execute Child Job from Parent Job (Job order issue)

@jlolling 

Thanks for the explanation, I was a bit confuse about subjobOK and componentOK....

 

I tried trigger child job from tDBCommit component but still the same result....

 

When the operation starts, the parent job create one table and then the child job started its operation (injecting PK constraint the to tables).

For simplicity, I wanted the parent job to finish running its operation before starting the child job.

Do you know how I could fix this?

 

I attached the jobs file for reference

runjob4.png

Highlighted
Moderator

Re: Execute Child Job from Parent Job (Job order issue)

Are you passing the same table to child job to create primary keys?
Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Eight Stars

Re: Execute Child Job from Parent Job (Job order issue)

@manodwhb
Yes, that's why I need parent job to finish its operation before starting the child job.
Highlighted
Moderator

Re: Execute Child Job from Parent Job (Job order issue)

How are you passing the table name to child job which is created and loaded in parent job.
Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Eight Stars

Re: Execute Child Job from Parent Job (Job order issue)

@manodwhb 

I don't need table names in child job so I didn't pass the table name to child job as you can see in the child job's tDBInput component below, 

**In the child job, tDBConnection_1 is the target db(MSSQL) for loading PK constraint and tDBConnection_2(MySQL) is the output database for injecting PK constraint.

 

Child Job

runjob2.png

 

 

The parent job copy tables from target database (MSSQL) to the output database(MySQL).

Then the child job read the target database(MSSQL)'s constraint and inject it into the output database(MySQL).

So basically I need the parent job to finish copying the tables(MSSQL->MySQL) before starting the child job.

(As the child job couldn't operate with a blank database).

 

That's how it work when operating the parent and child job separately,, would appreciate if you could guide me how I can make it work when starting the operation from parent job.

Highlighted
Moderator

Re: Execute Child Job from Parent Job (Job order issue)

As per my understanding,you need to pass the table to child job ,which is created as part of parent job. Then you take constrains based on that and it will create constrains.that's how you should create your flow.otherwise if your doing you will see table or view does not exits. Understand this that as part of parent job you created table a and loaded data.but in child job you are creating constrains for table b then it will throw the same kind of error. So your job design should be create table a and create constrains for the same table you will not get this error.
Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Eight Stars

Re: Execute Child Job from Parent Job (Job order issue)

@manodwhb 

Here I tried to pass the tables from parent job to child job, then run parent job,

but still the result is the same

 

Parent job's result

context00.png

 

Parent's job context param

context01.png

 

Child's job context

context02.png

 

 

Calling using the context in tDBRow Component (since DBInput doesn't need the parent's job output)

context03.png

 

If this is not the right way, please let me know...

Highlighted
Moderator

Re: Execute Child Job from Parent Job (Job order issue)

You need to use the context variable of Table name in tdbinput component and for the same table you need to create constrains.
Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Eight Stars

Re: Execute Child Job from Parent Job (Job order issue)

@manodwhb

Here I use context variable on Table name in tDBInput component and also in the output tDBRow component.

But still the result is the same. . . 

 

Are there any ways to fixed this?

 

Run Log

Spoiler

statistics] connected
Table Name = DOM_AIR_MIN_PRICELIST
dbo.DOM_AIR_MIN_PRICELIST|Table|Primary key|PK_DOM_AIR_MIN_PRICELIST|start_point_cd, end_point_cd, airline_cd, tour_kind, yyyymm
dbo.DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS|Table|Primary key|PK_DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS|yyyymm, mem_id, tour_id
dbo.DOM_AIR_MONTHLY_SEARCH_ROUNDTRIP_TICKETS|Table|Primary key|PK_WORK_DOM_AIR_MONTHLY_SEARCH_ROUNDTRIP_TICKETS|yyyymm, mem_id, tour_id
dbo.DOM_AIR_MONTHLY_SEARCH_TWO_ONEWAY_TICKETS|Table|Primary key|PK_DOM_AIR_MONTHLY_SEARCH_TWO_ONEWAY_TICKETS|yyyymm, mem_id, tour_id
dbo.DOM_AIR_PL_MONTHLY|Table|Primary key|PK_DOM_AIR_PL_MONTHLY|start_point_cd, end_point_cd, airline_cd, tour_kind, yyyymm
dbo.DOM_AIR_SEARCH_ONEWAY_TICKETS|Table|Primary key|PK_DOM_AIR_SEARCH_ONEWAY_TICKETS|mem_id, tour_id
dbo.DOM_AIR_SEARCH_ROUNDTRIP_TICKETS|Table|Primary key|PK_DOM_AIR_SEARCH_ROUNDTRIP_TICKETS|mem_id, tour_id
dbo.DOM_AIR_SEARCH_TWO_ONEWAY_TICKETS|Table|Primary key|PK_DOM_AIR_SEARCH_TWO_ONEWAY_TICKETS|mem_id, tour_id
dbo.DOM_AIR_TICKETS_PRICE|Table|Primary key|PK_DOM_AIR_TICKETS_PRICE|mem_id, tour_id, money_start_date
dbo.DOM_AIRLINE|Table|Primary key|PK_DOM_AIRLINE|airline_cd
dbo.DOM_AIRLINE0|Table|Primary key|PK_DOM_AIRLINE0|airline_cd
dbo.DOM_AIRLINE1|Table|Primary key|PK_DOM_AIRLINE1|airline_cd
dbo.DOM_AIRLINE2|Table|Primary key|PK_DOM_AIRLINE2|airline_cd
dbo.DOM_AIRLINE3|Table|Primary key|PK_DOM_AIRLINE3|airline_cd
dbo.DOM_MONEY_FOR_DA_ONEWAY_OUTWARD|Table|Primary key|PK_DOM_MONEY_FOR_DA_ONEWAY_OUTWARD|mem_id, tour_id, start_date, departure_time, arrival_time, flight
dbo.DOM_MONEY_FOR_DA_ROUNDTRIP_HOMEWARD|Table|Primary key|PK_DOM_MONEY_FOR_DA_ROUNDTRIP_HOMEWARD|mem_id, tour_id, start_date, departure_time, arrival_time, flight
dbo.DOM_MONEY_FOR_DA_ROUNDTRIP_OUTWARD|Table|Primary key|PK_DOM_MONEY_FOR_DA_ROUNDTRIP_OUTWARD|mem_id, tour_id, start_date, departure_time, arrival_time, flight
dbo.DOM_TOUR_FOR_DA|Table|Primary key|PK_DOM_TOUR_FOR_DA|mem_id, sub_id, tour_id
dbo.DOM_TOUR_FOR_DA_ONEWAY_SEARCH|Table|Primary key|PK_DOM_TOUR_FOR_DA_ONEWAY_SEARCH|mem_id, tour_id, money_start_date, money_end_date
dbo.DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH|Table|Primary key|PK_DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH|mem_id, tour_id, money_start_date, money_end_date
dbo.premium_bid_for_dair|Table|Primary key|PK_premium_bid_for_dair|id
dbo.premium_entry_mem|Table|Primary key|PK_premium_entry_mem|mem_id, product_type
dbo.tbl_site_mem|Table|Primary key|PK_tbl_site_mem|site_id, mem_id
dbo.tbl_site_mem_agent|Table|Primary key|PK_tbl_site_mem_agent|site_id, mem_id
dbo.DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS|ALTER TABLE dbo.DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS ADD CONSTRAINT PK_DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS PRIMARY KEY (yyyymm, mem_id, tour_id);
Table Name = dbo.DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS
AND the Query is = ALTER TABLE dbo.DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS ADD CONSTRAINT PK_DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS PRIMARY KEY (yyyymm, mem_id, tour_id);


Job Migration_DB_Parent_Job ended at 14:17 25/03/2020. [Exit code = 1]
Table 'dbo.dom_air_monthly_search_oneway_tickets' doesn't existTable 'dbo.tbl_site_mem' doesn't existTable 'dbo.dom_tour_for_da_roundtrip_search' doesn't existTable 'dbo.dom_air_monthly_search_roundtrip_tickets' doesn't existTable 'dbo.dom_air_search_oneway_tickets' doesn't existTable 'dbo.premium_bid_for_dair' doesn't existTable 'dbo.tbl_site_mem_agent' doesn't existTable 'dbo.dom_air_pl_monthly' doesn't existTable 'dbo.dom_airline0' doesn't exist
[ERROR]: local_test.primary_key_0_1.Primary_Key - tDBRow_1 - Table 'dbo.dom_air_monthly_search_oneway_tickets' doesn't exist
dbo.tbl_site_mem|ALTER TABLE dbo.tbl_site_mem ADD CONSTRAINT PK_tbl_site_mem PRIMARY KEY (site_id, mem_id);
Table Name = dbo.tbl_site_mem
AND the Query is = ALTER TABLE dbo.tbl_site_mem ADD CONSTRAINT PK_tbl_site_mem PRIMARY KEY (site_id, mem_id);

[ERROR]: local_test.primary_key_0_1.Primary_Key - tDBRow_1 - Table 'dbo.tbl_site_mem' doesn't exist
dbo.DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH|ALTER TABLE dbo.DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH ADD CONSTRAINT PK_DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH PRIMARY KEY (mem_id, tour_id, money_start_date, money_end_date);
Table Name = dbo.DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH
AND the Query is = ALTER TABLE dbo.DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH ADD CONSTRAINT PK_DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH PRIMARY KEY (mem_id, tour_id, money_start_date, money_end_date);

[ERROR]: local_test.primary_key_0_1.Primary_Key - tDBRow_1 - Table 'dbo.dom_tour_for_da_roundtrip_search' doesn't exist
dbo.DOM_AIR_MONTHLY_SEARCH_ROUNDTRIP_TICKETS|ALTER TABLE dbo.DOM_AIR_MONTHLY_SEARCH_ROUNDTRIP_TICKETS ADD CONSTRAINT PK_WORK_DOM_AIR_MONTHLY_SEARCH_ROUNDTRIP_TICKETS PRIMARY KEY (yyyymm, mem_id, tour_id);
Table Name = dbo.DOM_AIR_MONTHLY_SEARCH_ROUNDTRIP_TICKETS
AND the Query is = ALTER TABLE dbo.DOM_AIR_MONTHLY_SEARCH_ROUNDTRIP_TICKETS ADD CONSTRAINT PK_WORK_DOM_AIR_MONTHLY_SEARCH_ROUNDTRIP_TICKETS PRIMARY KEY (yyyymm, mem_id, tour_id);

[ERROR]: local_test.primary_key_0_1.Primary_Key - tDBRow_1 - Table 'dbo.dom_air_monthly_search_roundtrip_tickets' doesn't exist
dbo.DOM_AIR_SEARCH_ONEWAY_TICKETS|ALTER TABLE dbo.DOM_AIR_SEARCH_ONEWAY_TICKETS ADD CONSTRAINT PK_DOM_AIR_SEARCH_ONEWAY_TICKETS PRIMARY KEY (mem_id, tour_id);
Table Name = dbo.DOM_AIR_SEARCH_ONEWAY_TICKETS
AND the Query is = ALTER TABLE dbo.DOM_AIR_SEARCH_ONEWAY_TICKETS ADD CONSTRAINT PK_DOM_AIR_SEARCH_ONEWAY_TICKETS PRIMARY KEY (mem_id, tour_id);

[ERROR]: local_test.primary_key_0_1.Primary_Key - tDBRow_1 - Table 'dbo.dom_air_search_oneway_tickets' doesn't exist
dbo.premium_bid_for_dair|ALTER TABLE dbo.premium_bid_for_dair ADD CONSTRAINT PK_premium_bid_for_dair PRIMARY KEY (id);
Table Name = dbo.premium_bid_for_dair
AND the Query is = ALTER TABLE dbo.premium_bid_for_dair ADD CONSTRAINT PK_premium_bid_for_dair PRIMARY KEY (id);

[ERROR]: local_test.primary_key_0_1.Primary_Key - tDBRow_1 - Table 'dbo.premium_bid_for_dair' doesn't exist
dbo.tbl_site_mem_agent|ALTER TABLE dbo.tbl_site_mem_agent ADD CONSTRAINT PK_tbl_site_mem_agent PRIMARY KEY (site_id, mem_id);
Table Name = dbo.tbl_site_mem_agent
AND the Query is = ALTER TABLE dbo.tbl_site_mem_agent ADD CONSTRAINT PK_tbl_site_mem_agent PRIMARY KEY (site_id, mem_id);

[ERROR]: local_test.primary_key_0_1.Primary_Key - tDBRow_1 - Table 'dbo.tbl_site_mem_agent' doesn't exist
dbo.DOM_AIR_PL_MONTHLY|ALTER TABLE dbo.DOM_AIR_PL_MONTHLY ADD CONSTRAINT PK_DOM_AIR_PL_MONTHLY PRIMARY KEY (start_point_cd, end_point_cd, airline_cd, tour_kind, yyyymm);
Table Name = dbo.DOM_AIR_PL_MONTHLY
AND the Query is = ALTER TABLE dbo.DOM_AIR_PL_MONTHLY ADD CONSTRAINT PK_DOM_AIR_PL_MONTHLY PRIMARY KEY (start_point_cd, end_point_cd, airline_cd, tour_kind, yyyymm);

[ERROR]: local_test.primary_key_0_1.Primary_Key - tDBRow_1 - Table 'dbo.dom_air_pl_monthly' doesn't exist
dbo.DOM_AIRLINE0|ALTER TABLE dbo.DOM_AIRLINE0 ADD CONSTRAINT PK_DOM_AIRLINE0 PRIMARY KEY (airline_cd);
Table Name = dbo.DOM_AIRLINE0
AND the Query is = ALTER TABLE dbo.DOM_AIRLINE0 ADD CONSTRAINT PK_DOM_AIRLINE0 PRIMARY KEY (airline_cd);

[ERROR]: local_test.primary_key_0_1.Primary_Key - tDBRow_1 - Table 'dbo.dom_airline0' doesn't exist
dbo.DOM_AIR_TICKETS_PRICE|ALTER TABLE dbo.DOM_AIR_TICKETS_PRICE ADD CONSTRAINT PK_DOM_AIR_TICKETS_PRICE PRIMARY KEY (mem_id, tour_id, money_start_date);
Table Name = dbo.DOM_AIR_TICKETS_PRICE
AND the Query is = ALTER TABLE dbo.DOM_AIR_TICKETS_PRICE ADD CONSTRAINT PK_DOM_AIR_TICKETS_PRICE PRIMARY KEY (mem_id, tour_id, money_start_date);

 

tDBInput

 context04.png

 

tDBRow (Output -- need constraint)

 

context03.png

 

Highlighted
Moderator

Re: Execute Child Job from Parent Job (Job order issue)

somehow you are missing something in child job.
Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Highlighted
Eight Stars

Re: Execute Child Job from Parent Job (Job order issue)

@manodwhb 


I tried trigger tSubJobOk from tRunJob to tDBInput and it partially work.

But somehow, the last table's didn't have PK constraint injected. (in the output database, there's a table and its data, but no PK constraint)

 

When I check the log, it seems like the tables are created and its PK are loaded, but PK constraint are not inserted into the table.

 

Run Log

dbo.tbl_site_mem|ALTER TABLE dbo.tbl_site_mem ADD CONSTRAINT PK_tbl_site_mem PRIMARY KEY (site_id, mem_id);
Table Name = dbo.tbl_site_mem 
 AND the Query is = ALTER TABLE dbo.tbl_site_mem ADD CONSTRAINT PK_tbl_site_mem PRIMARY KEY (site_id, mem_id);

 

In the other job, I have experience a similar problem where the last table's data is not inserted to the output database.

At that time, I changed tDBOutput repository from using existing tDBConnection to manual setting and it worked.


So I tried to do the same for tDBRow's, but it didn't work, still the last table didn't have its PK constraint inserted.

And also below I also tried connecting tDBConnection with tDBCommit using OnComponentOK, but still no difference.

 

do you have any ideas?

dbcommit.png

 

 

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

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

Talend Cloud Developer Series – Deploying First Job to Cloud

This video will show you how to run a job in Studio and then publish that job to Talend Cloud

Watch Now

Talend Cloud Developer Series – Fetching Studio License

This video will help someone new to using Talend Studio get started by connecting to Talend Cloud and fetching the Studio License

Watch Now