How SSIS Dataflow really works?

Question

I have a SSIS kit that will transfer data from one database to the next. On an application server the SSIS program runs.

I am considering moving one of the two databases to another site. Will it have an effect on performance? How are the data flows in SSIS, i.e. do all the data go to the application server where the SSIS runs and then to the database for destination?

Answer 1

SSIS is a process on the client side, so if it runs on a server other than the machine running the DBMS the traffic will go over the network. Your query isn't very specific, but I guess you want to know whether moving a DB would affect performance because the SSIS program already runs on a separate computer.

If the SSIS job already runs on an application server which is a physically separate computer to the DB server so transferring one of the databases would probably have no effect on performance unless it has a significantly slower network connection than the other.

Check out Msbi certification training that enables you to master MSBI tools like SSIS, SSRS, and SSAS using SQL Server.