We are trying to setup a centralized SSIS server (SQL Server 2014). I want to know the following:
- Any procedure which is utilizing Linked Server and if that procedure gets called from SSIS SQL Task, is it better to put that logic inside SSIS itself without utilizing Linked Server?
- If we ask end user to put logic inside the SSIS package (i.e. JOIN...) and not use Linked Server, is it better performance wise?
Example: I have Table A from Server_1 and Table B from Server_2. If I am creating a stored procedure on Server_1, normally I will be joining Table A to Table B using Linked Server to Server_2. If I put this same logic inside the SSIS package and use [JOIN] task instead, would that be better?