Child table generation issue using sequential Foreign key from parent

One Star

Child table generation issue using sequential Foreign key from parent

Database connection is MS SQL 2005:
Having a problem generating and linking a child table to a parent table that is getting it's primary key generated sequentially with the Numeric sequential function. I have been able to populate the parent table successfully from a Salesforce instance, but when I try to add in the child table to pick up the addresses and the sequential accountId the database loading does not complete the process.
I have attached both the tMap image and the image of the Job flow. I am using the sequential function to generate the parent's testAccounts primary key for accountId and would like to map that accountId to the child table testAccountAddress.
When the job runs, it appears to be loading up 428 records to both tables, but never completes the process. When just populating the parent table the process completes successfully.
One Star

Re: Child table generation issue using sequential Foreign key from parent

Hi
Welcome to Talend Community!
I notice that you simply typed "testAccounts.accountid" in the field of testAccountAddress.accountid.
You cannot do it as this way.
Now here is a workaround.
Use two tMap components.
tSalesforceInput--main-->tMap--main-->tMap---
In the first tMap, what you need to do is to add incremental accountId column in OUT1.
Then in the second tMap, you are able to generate parent and child tables by simply dragging the accountId column from the left side to the right side of tMap.
Regards,
Pedro
Employee

Re: Child table generation issue using sequential Foreign key from parent

Two things. First, we need to be sure to get the foreign key values consistent between the two tables. Your current approach uses the function Numeric.sequence(?s1?, 1, 1) in two locations. Once for the testAccounts.accountId field and once for the testAccountAddress.accountId field. By design, every time you invoke this function you get a different sequence number. That?s great when you are using it within the same table. But you need the _same_ value for the testAccounts and testAccountAddress tables. You do _not_ want the sequence number to increment in these cases. If you were were writing just plain java you might say something like
long sequenceNumber;
long testAccountsAccountId;
long testAccountAddressAccountId;
for (Record record : recordset) {
sequenceNumber = getSequenceNumber();
testAccountsAccountId = sequenceNumber;
testAccountAddressAccountId = sequenceNumber;
// do stuff with TestAccounts table
// do stuf with TestAccountsAddress table
}
Note how the intermediate variable sequenceNumber is assigned the value of getSequenceNumber() exactly _once_ per each row.
To do this with tMap use the central column of the mapping UI to create intermediate variables. Then map the intermediate variable to testAccounts and testAccountAddress. Click on a the ?+? by Var as shown below to add a new intermediate variable, assign it a value using Numeric.sequence() and then map it to your tables.
The picture below shows a mapping that is similar to yours (not quite the same since I did not bother to set up the SFDC connectors).

The second thing you need to do is to make sure that the parent table inserts are done prior to the child table inserts. You can tell which insert will be executed first by looking at the order attribute which is shown in the title of the properties for the component. In the example below, the address output component has an order of 2. The testaccounts component needs to have an order of 1 so that as the parent table its rows will be inserted prior to the childs rows.
Note that in cases of bulk insert / update it may be more efficient from a performance perspective to disable the fk constraint, do the inserts, and then re-enable the fk constraint.