October 20, 2008 at 8:24 am
I have a biweekly SSIS job that moves eight MS Access tables to corresponding tables in a SQL Server database (after truncating the destination tables.) It runs from my desktop using Visual Studio and transfers data to a server-based SQL Server.
When I monitor the activity using Windows Task Monitor Networking graph, I see the transfer starting out like gang busters (80 MB/s), But the further it gets, when it is left with only two tables to transfer (the big ones as luck would have it, 500k rows and 2.1 M rows) the transfer speed drops down to 800KB / second; on the last table, the speed drops further to <500KB/sec. The profile is consistent, so I can't blame contention on my 100MB network.
While the last two tables have the most rows, they have fewer columns than the others. I am doing only a straight mapping, with no transformations, scripting, or anything fancy.
Can anyone guess what might be going on??
October 20, 2008 at 8:59 am
It is probably Access having trouble feeding up the records. Did you use an OLEDB source and select the table, or did you specify a query? Did you sort the data in the query? Is the access database badly fragmented?
October 20, 2008 at 9:14 am
Thanks for your suggestions. I'm pretty sure I can't blame Access -- all of the eight tables are from the same Access DB, and the smaller ones are transferred at a much faster rate. No fragmentation (that I am aware of -- all the tables are deleted and regenerated from make table queries each biweekly cycle, and I run frequent compact/restore actions), just using a OLEDB source (selecting MS Access within the connection manager, I assume that uses ODBC from the Jet Engine), no sorting or filtering going on. Within Access, the slow transferring tables respond quickly to opens, etc.
So I don't have a clue...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply