I use TOS to transfer a SQL Server table to another SQL Server. That works more or less. But I have one issue with truncating the table. In the properties for the output table I define “Truncate Table” for the table action and “Insert” for the data action. At the second run I get a lot of duplicate key errors. If I run the “TRUNCATE TABLE” manually in the SQL Server Management Studio, the job works fine.
Are there any known issues with truncate table? Talens Version is 5.3.2
I mimicked the scenario and it works fine in Talend Platform for data Management version 5.6.1. I cannot test it on the TOS, but perhaps you can upgrade to the newest TOS version and try again. To be thorough I tried it using separate connection components and built-in connections. The only difference is that using separate connection object requires a commit object.
The workaround I recommend is this:
- create a proc to truncate your table and call it from a tMSSqlSP component
- connect this to your original subjob which transfers the data between the two tables using an OnSubJobOK flow.
- In your tMSSqlOutput component (which performs the truncate/insert) in for Action on Table use Default (so it will not truncate the table)
- for Action on data use Insert
I tried this method and it works. This workaround will save you the time and frustration of dealing with the TOS issue.