May 3, 2011 at 5:41 pm
SSIS 2005 is way too slow.
It took me 30 min to transfer two MS Access tables,
each 450,000 rows, to SQL Server 2005.
Don't want to use it anymore.
Are there any other ETL good tools?
I saw Talend Demo somewhere on internet.
Looked pretty intuitive.
May 4, 2011 at 12:16 am
Maybe the problem isn't SSIS's fault.
How is the package created? What connectors did you use?
Did you do any transformations? Did you incorporate parallellism?
SSIS 2008 is also a bit faster. This is 2011 and you are using a 2005 product. Just saying 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 4, 2011 at 12:38 am
Koen Verbeeck (5/4/2011)
Maybe the problem isn't SSIS's fault.How is the package created? What connectors did you use?
Did you do any transformations? Did you incorporate parallellism?
SSIS 2008 is also a bit faster. This is 2011 and you are using a 2005 product. Just saying 🙂
Hey hey now, I'm still doin' 2k->2k5 upgrades... don't knock the old stuff. They keep me employed! 😀
However, I agree with Koen. More description as to what you're doing will help us help you figure out if you're dealing with an SSIS problem, or a design issue.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 4, 2011 at 1:32 am
Oh yeah, I forgot to mention:
SSIS holds the world record for fastest ETL.
Just saying 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 4, 2011 at 8:00 am
I am using
for MS Access connection:
Native OLE DB/Microsoft Jet 4.0 OLE DB Provider
(the actual mdb file is Access 2000 version)
For SQL Server 2005:
Native OLE DB\SQL Native Client
No transformations.
I'm using Data Flow Task with OLE DB Source for MS Access
and OLE DB Destination for SQL Server.
Super slow! About 5,000 rows per minute
May 4, 2011 at 11:27 am
JMI (5/4/2011)
I am usingfor MS Access connection:
Native OLE DB/Microsoft Jet 4.0 OLE DB Provider
(the actual mdb file is Access 2000 version)
My first question here is how fast can you usually pull out 5000 row chunks from this access database?
For SQL Server 2005:
Native OLE DB\SQL Native Client
No transformations.
No transforms is actually kind of a surprise. Do you use a query/view in the call to the Access DB or is it a single table connection?
I'm using Data Flow Task with OLE DB Source for MS Access
and OLE DB Destination for SQL Server.
Super slow! About 5,000 rows per minute
You're right, that's horrendous. I can usually pull 5-10k/second out of a link like that, even from MSAccess. That's not SSIS, there's something else going on. Network traffic/throttle, disk issues, concurrency problems... something. Can you describe or screenshot the connection information and the source/target properties?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 4, 2011 at 11:40 am
See the screenshot attached.
May 4, 2011 at 11:47 am
That unfortunately is merely the column maps. We'd need to see connection information, what type of load is it doing (fast or not), is it table locking, what the query is in F28 for the source, an idea of whatever object it is in F28 (query or table)... things like that. The devil is somewhere in the details on this one.
another thing to look at is while this runs find the spid on the target server and check its wait states. My guess is you're primarily dealing with a slow read instead of a slow write, but it's worth confirming.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 4, 2011 at 12:03 pm
Thanks a lot Craig!
"Data access mode" in OLE DB Destination.
That's where the problem was!
As soon as i switched to fast load
it took only 2 min to load 3 half a million records tables.
Thank you so much!
May 4, 2011 at 12:08 pm
JMI (5/4/2011)
Thanks a lot Craig!"Data access mode" in OLE DB Destination.
That's where the problem was!
As soon as i switched to fast load
it took only 2 min to load 3 half a million records tables.
Thank you so much!
My pleasure, glad we were able to get you fixed up. Thanks for the feedback on the solution. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply