Search code examples
sql-serverdatabasessisdata-transfer

SSIS Insert continuous data from one server / database to another

I have 2 different Servers (Server1 and Server2). On each is a database (db1 and db2) with a similar structure. There are continuous data inserts in db1.

I want a job extracting the data from one server (db1) to the other server (db2) on a daily schedule using SSIS and SQL Server agent for the scheduler. I only want transfer the new Data (the data which is not transferred in an earlier job) to the db2.

For this I want to select the last Id from db2 and use it to transfer only the data from db1 which have a higher id.

The concept itself works, but I don't know how to implement it with SSIS.

In SSIS I tried to use SQL execution tasks and safe it into variables:

First task (to safe the id in a variable): SELECT TOP (1) [ID] FROM [DB2] ORDER BY ID DESC

Second task (safe the rows into object variable): SELECT * FROM [DB1] where ID > ?

That works, but how can I insert the object variable into the db2.

Isn't there a cleaner way like:

Insert into Server2.[db2] select * from Server1.[db1] where ID > ?

The problem is, that SSIS only allows one connection for each SQL execution (same problem with dataflow tasks).

I also thought about temporary tables. But I'm not sure if this is the right way.

Would be awesome if someone got an idea / hint. I have not found much about this and I guess its an simple task.

Thanks a lot.

Solution

  • You are most of the way there. Just use a dataflow task instead of an execute SQL task. A data flow can have two different connections.

    1. Get the last id and put it in a variable with an ExecuteSQL Task. Then connect a data flow task

    enter image description here

    1. Configure the data flow task with a source and destination

    enter image description here

    1. In this example, we are using an OLEDB Source with a SQL command which accepts a parameter using "?"

    enter image description here

    1. We click on the parameter button and select the variable that we want to map to it

    enter image description here

    1. Finally, map the fields in the destination to the second server by clicking on the destination, selecting the server connection manager and table, and click on the mappings tab