July 24, 2009 at 9:58 am
Hi everyone, nice to be back.
I've been developing some SSIS packages which flags our client/lead base after import which sets things like TPS/MPS etc. We decided this would be desireble to do this person by person to reduce any locking issues, as there could be several packages/similar doing the same thing at any one time on the tables.
I've developed this using a for each ado container for the id of each person and run the required checks one by one. It works like a treat, and manages to process approx 500 people a minute which is ok (I would rather it be faster if anyone's got any ideas). However it seems to throw a wobbly if you give it 1000+ records, and the loop just seems to keep going round and round even though it's doing no processing at all and stopping the package causes VS/EX PKG UTILITY to crash.
I noticed that there was a bug raised with microsoft a couple of years ago for something similar (link) but this is with checkpoints which I'm not using, and was apparently fixed in the next version. (I've tried this in 2008, and it does the same thing).
Has anyone experienced this, or have any sensible alternatives?
Cheers
Laurence
July 24, 2009 at 10:29 am
July 27, 2009 at 3:19 am
Just SQL update statements. Typical would be a couple of deduplication querys, TPS, MPS and our internal Do Not Contact list.
July 28, 2009 at 4:09 am
bump
anyone got any ideas on this?
July 28, 2009 at 10:06 am
How do you know the loop is looping w/o doing anything? How can you be sure it is not just stuck in one spot waiting on something? Have you checked the DB locking/blocking while the job is stuck?
It seems like your job is not really doing any ETL type stuff and you've used SSIS to write a cursor. Any row-by-row processing you do will be vastly slower than set based processing. I would suggest for this particular task to write your logic into a stored procedure.
You mentioned that you thought that row-by-row would help reduce locking/blocking, but my exerience has been that cursoring through the rows increases contention. It sounds like you didn't actually have problems but that you decided to cursor to help avoid problems. I would suggest moving your process to a stored procedure that works off of the set of data and then working through locking/blocking problems as they arise.
Is there any reason why your work needs to be done in SSIS?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply