January 5, 2011 at 9:10 am
Hi,
I am using an ADO.Net destination in an SSIS data flow task to import 25,000 rows from a CSV. Each row is 200-250 bytes in size, containing a mixture of text, integers and dates, and one money column.
Out of the box, the import generally fails with a timeout after one burst of 10,000 rows. I adjusted the DefaultBufferMaxRows to 1,000 and it will now usually (but not always) churn through the import, but takes a full five minutes to run. This is the same no matter where the package is run, including on the target server.
What can I do to improve this dire performance with the ADO.Net destination in SSIS? There is no magic fast load option so that is out.
Thanks.
January 6, 2011 at 7:08 am
What is your destination? Is there a reason why you are not using OLE DB?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 7, 2011 at 1:26 am
My destination is SQL Server 2008. I used ADO.Net as it was the most modern technology so I assumed it would at least work well, if not offer some benefit. What I didn't expect is that it would work so badly as to be unusable. If it is such a bad choice, why is it available at all?
I ended up re-writing the package to use OLE DB and it now works in a few seconds as expected. It's a poor advertisement for ADO.Net as a technology though that it can't sensibly be used within a mainstream microsoft product.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply