In SSIS there is one task Execute TSQL task which is similar to Execute SQL task. We will see what is the difference between the two.
Execute the TSQL Task :
Pros : Takes less memory, faster performance
Cons : Output into variable not supported, Only supports ADO.net connection
Execute SQL Task:
Pros : Support output into variables and multiple types of connection, parameterized query possible.
Cons : Takes more memory, slower performance compared to the TSQL task.