So the general approach we tend to take with Execute SQL Task is if it's a short putt, then straight T-SQL. If it's more complex, then EXECUTE a SQL Server Stored Procedure. Like we normally would not create a SQL Server Stored Procedure for a straight INSERT-SELECT...but if there is logic involved, then we will put together a SQL Server Stored Procedure and EXECUTE that from the SSIS Execute SQL Task.
Just wondering what other opinions are on this.
Just had a discussion with our DBA who claims any SQL Server Entity whether in SSIS or not should have an associated SQL Server Stored Procedure. And I explained to him that if we simply TRUNCATE a Table and then do an INSERT-SELECT...And he said that that should all be in a SQL Server Stored Procedure.
Seems like a little overkill to me.
Community thoughts.....