question

BobbyP-1695 avatar image
0 Votes"
BobbyP-1695 asked ZoeHui-MSFT answered

Execute SQL Task and straight T-SQL or a SQL Server Stored Procedure

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.....

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RyanAbbey-0701 avatar image
0 Votes"
RyanAbbey-0701 answered

Personally both would frustrate me, both sound like they're only using SSIS as an orchestrator when SSIS is pretty good at allowing someone to understand what the logic is doing.

The only thing I find more frustrating than going in to SSIS and finding the logic is in a stored procedure is having to understand what someone was thinking when they wrote the stored procedure

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @BobbyP-1695,

The Execute SQL task can be used in combination with the Foreach Loop and For Loop containers to run multiple SQL statements.

These containers implement repeating control flows in a package and they can run the Execute SQL task repeatedly.

For example, using the Foreach Loop container, a package can enumerate files in a folder and run an Execute SQL task repeatedly to execute the SQL statement stored in each file.

More details you may refer: execute-sql-task

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.