In the SSIS data flow task, I've been importing CSV files to my SQL Server via Flat File Source -> OLE DB Destination.
When I'm setting things up in the package design preview, if I have just one import set up, it's pretty slow; usually 1.5 seconds per 1,000 rows. Now if I drag and setup a 2nd Flat File to OLE DB Destination import to the right of it - same criteria as the first - it's lightning quick. I've actually started setting it up a dummy import in the upper left of the package design with a relatively small # of rows, like 50,000, just so I can quicken the pace on the other imports that are 1 million + rows.
Is this recognizable to anybody? I find it very unusal and assume I'm doing something wrong in the process. Sometimes I only want to import one CSV at a time in my package, but I have to set up at least 2 since the first item in my package preview screen always imports so slow.
edit on further review - I found the slowness is not caused by where the import data flow is placed in the preview screen, but which OLE DB destination is set up first on the connection manager is the slow one. When I created a 2nd import, I tried deleting the original OLE DB destination (default name "OLE DB Destination") on the Connection Managers tab and using the 2nd iteration (default name "OLE DB Destination 1") for both imports, and they both imported quickly.
EDIT #2 - ok, so apparently on default it was loading my first OLE DB destination as "Table or View" data access mode, and the subsequent ones as Table or view - fast load" data access modes...so problem solved. Nobody look at me, I'm a moron.
