Eirikur Eiriksson - Tuesday, June 12, 2018 10:21 PM

george_at_sql - Tuesday, June 12, 2018 9:00 PM

Hi 
I have a table in SQL server which has over 5 million records and around 12 columns and wish to load it into a table in another SQL server.

There are no transformations of data and the structure of the table in source and destination is identical.

There are no indexes in the source table or have any column whereby i can split up the 5 million records into "partitions" in which case i could have parallelized the data load.

I tried using SSIS project with source as SQLServer and destination as SQLServer, however it is taking more than 3-4 hours and was erroring out after 1 million records.

After that i tried source SQLServer and destination as flatfile. It didnt error out however it was taking a lot of time(>5 hours) and i dont know if this is a feasible option in the actual production environment.
The idea is once the flat file is loaded i would import it using SSIS into the destination SQLServer table.

Any other options which are available that can speed up the process of data loading?

How about using backup and restore?
😎