How to know if data is committed after an insert

Question

How to know if data is committed after an insert

dplaut 49149

SSC Eights!

Points: 820
More actions
November 28, 2015 at 7:42 am

#305501

We have an SSIS package that is just inserting data into a table.
This is run by an SQL server agent job.
The next step in that job is to use the data just loaded.
It is obvious from what I'm seeing that the next step does not have the data in the table just loaded. If I wait a few minutes and restart the job at that next step then the data is available. So I'm guessing that the package is finishing but the data is not yet committed.
Am I correct and how do we find out when the data is committed so that we can run the next step (other than programming an arbitrary wait step)?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lempster SSCoach Points: 15651 More actions · Answer 1

How much data (how many rows) are being inserted? I assume this is into an OLEDB Destination so do you have FastLoad enabled? On the Data Flow Task, how many rows are shown as flowing into the Destination?

Regards

Lempster

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

dplaut 49149 (11/28/2015)
It is obvious from what I'm seeing that the next step does not have the data in the table just loaded.

What is it that you're seeing to make you think so? And, are you sure you're not just seeing some problem with a "screen refresh" somewhere along the line? Or, is you job step calling another job, which WOULD run asynchronously?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dplaut 49149 SSC Eights! Points: 820 More actions · Answer 3

Next step uses the data in the table that was loaded by SSIS.

Next step will be missing an entire fiscal quarter of data unless I put a step in between to wait 5 minutes (waiting 1 minute was not enough). i.e. wait 5 minutes after SSIS completes before going on to the next step.

Yes, FastLoad is being used. Yes, it is an OLE DB destination: SQL Server 2012 table.

There are almost 2 million rows in the table that SSIS loads. It is a wide table.

Lempster SSCoach Points: 15651 More actions · Answer 4

Ah, got you (I think). So the SSIS package is not failing, but you have another job step that kicks off before all the inserted rows have been committed. In that case you want to take a look at some of th FastLoad properties in the Advanced Editor of the OLEDB Destination. Specifically there is a property called FastLoadMaxInsertCommitSize which has a default value of 0. Contrary to what you might think, a value of 0 means the complete opposite of "don't wait for any rows to be inserted before committing them", it actually means "wait until all rows have been inserted before committing them".

If you change the value to 1000, 10000, 50000 or whatever, those number of inserted rows will be committed as a batch.

There are 3rd party monitoring tools such as SQL Sentry's Performance Advisor that offer features like SQL Agent job chaining that is not available within SQL Server natively...unless, as you stated, you use WAIT FOR.

Regards

Lempster

dplaut 49149 SSC Eights! Points: 820 More actions · Answer 5

Thank you, Lempster. Changing these values from the default worked. (and taught me something new.)

MMartin1 One Orange Chip Points: 27768 More actions · Answer 6

Just curious, to move on to the next step... the default behavior is for the current job step to complete reporting success. Are these steps you refer to in the same job? If not why would they not be if they are logically dependent?

----------------------------------------------------

dplaut 49149 SSC Eights! Points: 820 More actions · Answer 7

Yes, the next steps are in the same sql server agent job.