I have a job that is using tmssqlcommit to commit every 10000 rows.
My source is bringing in 11,856 records and it is only committing 10,000 records.
When I select Auto commit in tmssqlconnection it will commit all 11,856 records. When I uncheck, it will only commit 10,000 rows. What can be a problem?
NOTE: tmmsqlcommit is unchecked for close connection.
would be good to include examples of Your Job, because - tmssqlcommit haven't any settings for number of records, also tmssqloutput haven't number of records for commit when use connection
Normally when You use tMSSQLOutput with tMSSQLConnection You use only batch size, but single commit at the end
When you said "You use only batch size, but single commit at the end". How do you setup a single commit at the end? I'll have a tmssqlcommit which will commit the # of records defined in Batch size.
tMSSqlCommit doesn't have any batch size property to be defined.
The batch size property would be found for tMSSqlOutput -> advanced settings and you could see there the "Use Batch Size" property could be checked with a default value of 10000. What you need to do is when your write operation finishes completely (assuming you have the complete operation in a subjob) use the tMSSqlCommit component and connect it from the subjob on "On SubjobOk" trigger.
Better U should go for Use Batch Option available under your DBOutput Component Advanced Settings and Check AutoCommit in Your Connection Component. One More Thing U can do. If u are not checking AutoCommit in your Connection Component then Use tYourDBCommit Component in tpostjob with CONCOMPONENTOK.Though I would recommend best approach is to do AutoCommit.
what if we do not use the Connection component? I am facing the same issue but i have used just the tDBOutput component. any idea why this must be happening?
