Copy data from a large table on a test server to a table on production server in SSIS

Question

I want to copy data from a table in our test server to a table in our production server. The table has 50 million rows in it. One of the ways I was planning on doing it was to have two tasks:

1. Use the data flow task to export the data from the source table to a CSV file.
2. Use the Bulk Insert flow task to read from the CSV file and insert it into the destination table.

Is there a better way to do this in SSIS? Thanks!

Answer 1

There are many approaches to transfer data between two servers. SSIS is not always the preferred one. Noting that 50 million rows are not always considered a large data set; It depends on the server resources, columns data types, and other factors.

The simplest way to import/ export data is to use the SSMS Import/Export wizard. Another approach is to use BCP as @Nick.McDermaid mentioned in the comments.

If you have limited physical resources, and you need to do this using SSIS, you can try loading data in batches as explained in the following article:

• SQL OFFSET FETCH Feature: Loading Large Volumes of Data Using Limited Resources with SSIS