Two tDBConnection components, one doesn't work

[PhilHibbs]

I have a simple extract/transform/load job. It has two tDBConnection components, one for extract and one for load, hooked to a tPreJob, then a read/transform/write subjob.

The two tDBConnections actually connect to the same database, but with different default schemas. I want two separate connections so that they could in theory be different databases.

The extract works fine, and the load appears to work but the destination table doesn't get created. I don't think the data goes anywhere, if it is being created and loaded somewhere then I don't know where that is!

If I use the same tDBConnection for both the extract and the load, then it works and the table is created and the data loaded.

It doesn't matter which of the two tDBConnection objects I use - if I delete the extract connection and point the extract to use the load connection, then it extracts and loads just fine using the load tDBConnection that didn't work earlier!

is there anything about the tDBConnection component that would cause a second one in the tPreJob flow to simply not work?

[Anonymous]

Hello @Phil Hibbs​ ,

As you use different schemas for the 2 tDBConnection, so please double check if the destination table is created in the related schema in database side?

Thanks

Aiming

[PhilHibbs]

Yes, the source tables are in the source schema, the destination tables are in the destination shcema. If we use two different tDBConnection components to connect, then anything writing to the second tDBConnection does not end up being written (or is being rolled back). If we use built-in connection details, then it works. And if we delete the first tDBConnection and use built-in for the source schema, then the destination loads also work so it isn't the connection or the database schema that is at fault.

[Anonymous]

It sounds weird, they are completely two different database connections. I want to see your job design, can you please share a screenshot of Job?

[mks02]

Hi @Phil Hibbs​ 

I guess data is not committing in the db tables. Please check any of these below:

1) On tDBConnection got to Advance settings --> check the AutoCommit box.

2) alternatively after tPostJob you can use tDBCommit to commit the DB transactions.

Thanks!