I have developed an SSIS package that loads data into an OLEDB data destination table from 26 very small fixed format flat files which should, you would think, run very quickly but doesn't (we have a standalone .exe that does the same work in seconds).
It seems to pause around 30 seconds between each file iteration (regardless of whether there were any detail records to load in the file) in order to "commit the insert", or perform the "Post Execute " and/or "Cleanup" phases, or all of those. I have tried tweaking every property setting for the data flow task itself and the items within the data flow (buffer sizes, max records, etc.) with absolutely no luck. I have even tried flowing the data to flat file destination (rather than an OLEDB database table) and it still wants to pause between each file.
I'm almost at the point of abandoning the idea of using all the neat tools that SSIS provides, and just using a big VBSript task (almost like what we use now) to do all the work.
Any suggestions? ...