Talend tmssqlrow seems not execute transaction

I'm facing problem with a job in Talend. I use a tmssqlrow to delete a large amount of data in some tables (+/- 250 000 000 rows). As I have some condition, I can't use a truncate.

The database is set with option recovery mode simple. Despite this the transaction log file increase still take all free space from disk.

here is the code use:

DECLARE @RowCount int = 1, @nbocc int = 0 
WHILE @Rowcount > 0 
BEGIN 
   BEGIN TRANSACTION 

   delete TOP (100000) from large_table

   SET @Rowcount = @@ROWCOUNT;
   COMMIT TRANSACTION
   CHECKPOINT

   SET @nbocc = @nbocc + 1 
   IF @nbocc = 15 
   BEGIN    
      DBCC SHRINKFILE (N'logfile' , 0)
      WITH NO_INFOMSGS 

      SET @nbocc = 0 
   END
END

SET ROWCOUNT 0

If a run this script in management Studio, it works fine, so the ldf file is shrink every 15 occurences. But using with Talend, the ldf file constantly growing.

How I can fix that? I try to put a tOpenconnection, then tmssqlrow, then tmssqlcommit and tcloseconnection, but facing same issue.

Solution

From what I've seen, tMSSqlRow doesn't always behave as expected with sql scripts. You should encapsulate your script in a stored procedure, and then just call the stored procedure from tMSSqlRow (EXEC myStoredProcedure..), or a tMSSqlSP component.