Copying data from one database to another using SS

问题:

I am writing a SSIS package and trying to extract the data from one database to another. I have created a Execute SQL task and using the following statement INSERT INTO dbo.getParties EXEC dbo.getParties to perform the operation. This works when the source and destination our in the same database. How do I handle this when the source and destination are in different databases. You can associate only one connection manager to a task.

Do I connect to the source and in the SP call the destination. Is it the right way of doing it

Below is the design of my template

回答1:

Use Data Flow component where you can set up the source and destination connection strings.

..and inside the data flow task use the Destination and Source Assistants that let you define its own connection string for each.

Of course, besides these you can also apply any sort of data transformations you wish.

Edit: Since you your source is SQL Command (stored procedure) you need to define it in your source assistant. As you can see here, just change Data Access Mode to SQL Command and set to EXEC getParties:

回答2:

In the Execute SQL Task INSERT INTO Command just add the Database name to the Table name. ex:

INSERT INTO Db1.dbo.Table1
SELECT * FROM Db2.dbo.Table2

Side Note: i think that it is better to use DataFlow Tasks to copy data, because it is faster and provides more controls and error handling