import data from another server

Question

How do you import data from querying data on server1 to a table on server2 in SSIS?

Answer 1

Here are some more options in addition to @ZoeHui-MSFT solution of using SSDT or SSIS or direct query

(1) Use bcp to export the data to csv file and import the data to the other server - this is my preferred option for many cases

(2) Use merge replication - fits mostly when this is not a one time work but a case that you want to keep the tables sync

Answer 2

How do you import data from querying data on server1 to a table on server2 in SSIS?

With a data flow task and OleDB data source and destination, see https://learn.microsoft.com/en-us/sql/integration-services/control-flow/data-flow-task?view=sql-server-ver15

Answer 3

Hi @Winston TheFifth ，

If you had configure server2 as linked server on the server1, you can run the following statement inside stored proceduce to copy table data.
Insert Into Server2.Database2.dbo.Table2 (Cols)
Select Cols From Server1.Database1.dbo.Table1

Or you can use the SQL Server Import and Export Wizard to export the data from server1 to server2, save the SSIS package created by the wizard on the SQL Server, create a job to run the SSIS package.

import-and-export-data-with-the-sql-server-import-and-export-wizard

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

ado.net worked