Share via

SQL Server Stored Procedure runs in seconds...but not in SSIS Package it takes minutes

Bobby P 221 Reputation points
2022-07-01T18:49:01.52+00:00

When we unit tested our SQL Server Stored Procedure, it runs in seconds. Yet the same SQL Server Stored Procedure when executing in our SSIS Package in a Data Flow and an OLE DB Source is taking minutes.

Actually while I was waiting and questioning everything and maybe considering a database lock or something while the SSIS Package was running, I EXECed the SQL Server Store Procedure in SQL Server Management Studio and again it ran in seconds.

Why is the same SQL Server Stored Procedure in the SSIS Package taking so long to run?

Any insight would be greatly appreciated.

And Thanks for your review and am hopeful for a quality reply.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,525 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 107.2K Reputation points
    2022-07-01T21:27:51.417+00:00

    Try the effect of running

    SET ARITHABORT OFF

    before your run the procedure in SSMS. If my guess is right, it will be slow in SSMS as well.

    Most likely it is related to parameter sniffing, and I discuss this in detail in the article Slow in the Application - Fast in SSMS?.

    0 comments
  2. ZoeHui-MSFT 35,556 Reputation points
    2022-07-04T05:45:12.56+00:00

    Hi @Bobby P ,

    Please try with below methods to see if it could improve the performance:

    1.Ensure connection managers for OLE DB sources are all set toDelayValidation ( = True).

    2.Ensure that ValidateExternalMetadata is set to false

    3.DefaultBufferMaxRows and DefaultBufferSize to correspond to the table's row sizes

    4.DROP and Recreate your destination component in SSIS

    You may also refer to ssis-package-takes-a-long-time-to-run to see if it could give you some ideas.

    Regards，

    Zoe

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    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.

    0 comments