June 1, 2012 at 12:09 pm
this is more of a ssis question but i post it here anways as it may be solved through tsql commands. my query is, suppose I have one package that migrates 100 million records from one server to another and it is bulk insert package. I would like to have a mechanism to monitor in a way that if the package fails at the very last stage say for example after importing 99 million records due to network issues- can I start the job from after the last inserted rows instead of repeating the whole 100 million records again. I do not have any indexes in the target server and( I think) I cannot implement checkpoints here because it’s a direct bulk insert. Any advice?
June 1, 2012 at 2:02 pm
Never had this issue so just brainstorming here. You could redirect failed rows to an error file, error in that they failed to load due to the network error not they are malformed per the destination although you won;t be able to tell the difference from looking at the error file. What is the Commit Size on your OLE DB Destination, 1MM? In your package you could check for an error file after your initial DFT completes and if one exists try loading it again. If successful delete the error file and the next run will start anew. Just thinking aloud...there may be better options.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 1, 2012 at 4:59 pm
You can specify a batchsize for a bulk load, and SQL then commits every time after that many rows are loaded (obviously you definitely want to specify a batchsize for such a large load).
Say you specify batchsize of 100,000. Then, if the jobs fails at 99M out of 100M, the first 98,900,000 are all safely committed and do not get rolled back.
The tricky part is restarting the load at the correct point, and loading only the additional data that needs loaded.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply