Yes, I would like things done in one job also. In fact, if I could have all of my work in one job, what a dream when putting it into the enterprise schedule. Imagine, one job!
One job to rule them all, one job to find them. One job to bring them all and in the darkness bind them! Bwahhhahahaaa Bwahhhahahaaa
Excuse me, Evil Ken came out there.
Here's some friendly advice. Get over the pretty picture sales pitch of one job. Small, modular, easy to use jobs. Trust us on this one. If you look at the people belonging to the Inner Circle, you could probably find 10,000 jobs developed collectively. We know a little bit about using this product.
So, not only are you stacking a ton of database work in a single job, you also are going to have to deal with performance. You've already been told you're going to have locking issues, as well as inter-table contention.
(Sounds of my chest puffing out)
In my expert opinion, you should separate your insert, update, and delete file into three separate files. Then, bulk load your inserts. Then, update your updates. Then, delete your deletes. You can have one job to separate the complex file. Wow, here's an idea, instantiate that job so that you have N jobs instances separating out the file using N cpus, instead of 1 job using 1 cpu. Then, concatenate your insert files together and bulk load that. Gee, I bet that's really fast. Then, concatenate your update files together and run those in. Then, concatenate your deletes together and run those in. Use a job sequencer to run these in the right steps, with the concatenations as command stages.
One thing to consider: bulk load your updates into a work table and use a SQL statement to perform the update using parallel dml. Same trick works for the delete.
Bawango...
![Cool 8)](./images/smilies/icon_cool.gif)