crazy4sql (6/15/2012)
This took approximately 1 hour to load 168,261 rows.When I used a linked server and a Select into statement it took 10 seconds.
This should not be the case. This difference is quiet huge. Are you sure both activity is doing the same thing.
It doesn't surprise me at all. The original SSIS package was, most likely, doing 168,261 commits, whereas the SELECT INTO using the linked server only does a single commit. That is a huge difference in the processing required. The change I suggested to the SSIS package is intended to change the default behaviour of the ole db destination transformation to be similar to the SELECT INTO - although you actually have some control on how often the it commits.