August 3, 2009 at 2:11 pm
Set-up: Windows 2003(virtual server), SQL Server 2005 sp3, SSIS installed on the same box.
4 gbs of ram, 2 processors.
job: nightly import of tables from oracle database
package: a bunch of containers having data flow tasks. on small tables truncate the table and reload it from sql select statement into sql server destination. on bigger tables, delete any data that was changed the day before (on sql server), insert new records from sql statement into sql server destination, download all primary keys, compare the downloaded keys to the ones on sql server and delete the records that are not in oracle table.
problem: sometimes the package can run for days without actually doing anything. there's no blocking that i can see, activity monitor shows "bulk insert" as runnable, no activity on profiler whatsoever related to the package/job. it doesn't fail, doesn't update the tables, just sits there. As far as I can tell, sometimes it does that on small tables (truncate + insert operations), sometimes it does that on big tables (see above for steps).
Does anybody have any ideas?
August 3, 2009 at 2:23 pm
Forgot to mention that the destination database resides on SAN. I'm currently looking into getting SAN IO and network utilization tested. It still doesn't explain a table of 2 mill records never finishing downloading.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply