I am using SSIS to import a 25,000 row CSV to an empty table. The table has a primary key that is populated as the data is inserted. I am using an ADO.Net destination.
What reasons are there why this operation would take around 5 minutes? At the default value of DefaultBufferMaxRows (10,000), the import fails completely with a timeout after around 5,000 rows. I dropped the value to 1,000 and now it gets there eventually with no timeout, but takes a full five minutes.
The Flat File data source has explicit column widths set, could that be the issue?
Best Answer
The 'answer' was that the ADO.Net providers in SSIS are basically useless and an embarrassment to the '.Net' moniker. They flake at more than a few thousand rows and are so hobbled as to be effectively useless. Basically, just stick to the old OLEDB providers and everything will be fine.