today I want to share some information on how to import some big chunks of data from one server to another. Need for this movement for me personally arised when I had a need to transfer big amounts of data on a regular basis. Big amount means 2.5 millons of records. My first apporach was to use SQL Server 2017 Import and Export Data. Initially I've used 32 bit tool, and found out that I sometime face strange error messages. After some googling I've found that for big volumes of data it's better to use SQL Server 2017 Import and Export Data 64 bit.
and all of the sudden strange configuration errors, drivers lack error, ODBC driver error had leave me.
So, one of the lessons learned is check in your system which versions of app you have, and if you have 32 and 64 versions, then most probably you'll need to use 64 bit version.
Another lesson learned, is for importing data from SQL Server database to SQL server database, the best option which will work is to use SQL Server Native Client. If you execute SQL Server Import and Export Wizard, it can look like this:
I will omit description of stes that describe configuration of source table, destination table because they are relatively straightforward. But I want to point attention to checkbox Save SSIS Package:
as you can see, I've ticked Save SSIS Package, and added some kind of password. Next step it will ask for credentials on which db you want to save SSIS package:
After those steps, you can use object explorer to log in to Integration Services like this:
Enter credentials in window that will appear and then you'll see your created package at MSDB node:
At any moment of time you'll be able to right click on it, choose Run Package menu item, enter password and execute your import again and again without need to specify all needed data again and again.