Thursday, January 13, 2011

SSIS - Access Mode...

I was working on a fairly simple SSIS package, as part of the package I had to move data from a SQL Server Database to another SQL Server Database. I used a Data Flow to do a simple copy of the data using OLE DB Source and Destination. When I executed the package, I found that the performance of the Data Copy was very slow. What was puzzling was that the number of rows to be copied were just around 1000 rows and the table was not very wide. Based on research from other blogs, there is a property called Access Mode in the OLE DB Destination Data Flow component. I Set the AccessMode to OpenRowset Using FastLoad and then re-ran the package. The data flow performed the copy of data a lot more faster and the package completed in a much quicker time.

1 comment:

  1. SQL Server Destination

    SQL Server Destination We have looked at two different ways to import data into SQL Server—using the Bulk Insert Task and the OLE DB destination earlier in this chapter. Though both are capable of importing data into SQL Server, they [...]

    for more please visit us