Hi, I think I've encountered a bug in the way Talend handle [database schema] with MSSQL connexion. Here is the situation:
- Business Requirement: Use an unique transaction to insert data into two target tables, then commit.
- Those two tables are in two distinct database schemas within the same DB. Example: - SchemaA.MyTableA
- SchemaB.MyTableB
My Analysis:
- In the tDBConnect, it seems that if the "Schema Property" is not specifyed, only the tables from the schema "dbo" can be accessed throught an tMSSQLOutput. Rq. by default, when the schema is not specified, SQL Server seek from the "dbo" schema. - To use tMSSQLOutput for a table of a schema different that "dbo", that schema has to be specified in the tDBConnect. However, when a schema is specified, tables from other schemas cannot be accessed throught tMSSQLOutput. Example : if tDBConnect's dbSchema is set to"SchemaA", "SchemaB.MyTableB" is not accessible through tMSSQLOutput.
- The "table" field of tMSSQLOuput do not handle schemas prefix. Having "SchemaA" in tDBConnect, setting "SchemaB.MyTableB" in tMSSQLOutput throw the error:
java.sql.BatchUpdateException: Invalid object name "SchemaA.SchemaB.MyTableB'
The Conclusion I've reached:
- The tables reachable through tMSSQLOutput are schema-scoped
- So, to insert into tables from distinct database schema, we have to use distinct tDBConnect. Hence, we cannot insert into those tables using a single transaction when the tables are on different schemas.
If someone confirms that, I think may be is a critical bug. Our business requirements specify that insert into those two tables has to be performed within a single transaction to make sure that data integrity is met.
If it is a complex single transaction, you can build a stored procedure and call the stored procedure from Talend. Many of our customers follow this method of processing the data especially if the entire data has to be processed s single block.
Warm Regards, Nikhil Thampi
Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
If it is just two tables, why don't you use treplicate to create multiple datasets from same input data and send it to multiple output tables? You can also use multiple outputs of TMap to do the same component.
You can also think about dynamic schema to do generic ingestion but in your use case, the first method is a better choice.
Warm Regards, Nikhil Thampi
Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Using a tReplicate will not met the business requirement because the inserts into the output tables should be performed in a transaction. If one of the two insert fails, the entire process should be rollbacked. The point is, because the two tables are on different database schema, we can't share the same tDBConnect for the two output tables, we have to use two different tDBConnect. So if an insert in one of the two table fails, the inserted data will be rollbacked only for one of the two table, the other one will still have the data that has been inserted to it.
Being able to target tables located on multiple database schema within the same tDBConnect would solve the issue. We could then use the same tDBConnect and have them into the same transacion by unchecking the 'Auto Commit' setting in tDBConnect's Advanced Settings.
(Rq. For precision, I talk about database schema, not Talend schema :
- Talend Schema = the metadata about the column.
- DataBase Schema = a logical grouping of table - ex: for security reason).
If you are not using Auto commit, tthen you can still do the commit and rollback for multiple DB transactions.
You need to do the DB commit only if both transactions are successful using On SubJob Ok else you can do Rollback for both tables in one go.
Warm Regards, Nikhil Thampi
Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
If you are a subscription customer, you can raise the feature request through case system where support team will liaise with Product team and confirm whether it can be added to the product road map.
If you are an open source customer, please use the below link to create JIRA ticket for your feature request.
Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
I come back to what I said, this will not solve the issue because of the following point:
- Those inserts are in reality part of a bigger process and are the final step. Those tables have FK to a third table populated within the same transaction. So the use case is to fill a Parent table then two Child Tables, each child in a different schema. And the all picture within the same transaction. Those Child Tables have a FK to the Parent table. [It happens that one of the Child share the same database schema than the Parent].
- If a second transaction is openned to handle the second schema, the Parent's PK can not be seen by that transaction until the first transaction is commited, while trying of insert we'll get a FK constraint violation. So that second table have to be part of the same transaction in order to see its Parent's PK.
If it is a complex single transaction, you can build a stored procedure and call the stored procedure from Talend. Many of our customers follow this method of processing the data especially if the entire data has to be processed s single block.
Warm Regards, Nikhil Thampi
Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
I'll nevertheless open a Jira ticket for a feature request. We would have liked to take advantage of Talend's graphical interface to perform that integration.
Thank you very much nikhilthampi for your time and have a nice day.
