I have just installed the Microsoft SQL Server Data Tools for Visual Studio 2017 on my workstation and created a simple SSIS package (.dtsx) which basically copies data from one MSSQL server to another.
Now I am wondering if the data will travel through my workstation or if there is direct connection established between the two servers. Thanks in advance!
Best Answer
The data will travel through where the SSIS package is running. In dev this will be your workstation, once deployed this will be where ever your package(s)/project were deployed to. SSIS will not produce a direct connection as you describe between two data sources of any type
This is true even if there is no other processing involved beyond "read from here, write to there", and there are practical and security reasons for this. Practical reasons include mainly the fact that most data sources/sinks don't support remote connections to other resources like that, and the security concern would be that even if SSIS could instruct a data sink to directly talk to a data source (or vice versa) it would have to hand over security credentials in order for it to know how to.
Of course if your SSIS packages are deployed on one of the servers in question then there is effectively a direct connection, but the data will still go through the SSIS process rather than being sent directly to the SQL Server instance. Furthermore, even if you are moving data between tables in the same database (so the same database on the same SQL instance is both the source and destination of an ETL process) the data will still leave SQL Server, be processed by SSIS (however minimally) then sent back to SQL server.
If you need direct communication between SQL Servers then you can use the linked server feature to read/write data directly, but this would not allow SSIS's other processing features. Also this will not work on Azure SQL. There is a per table/view option that will work on Azure SQL (and newer on-prem SQL instances) in external data sources. Both these options require at least one of the SQL instances to have credentials for accessing the other, which may be undesirable depending on your situation and security model.