none
How to improve SSIS package performance RRS feed

  • Question

  • Hi Team,

    Have created a 30 tasks in single SSIS package. It taking more than 2 hours to load data. Could you please help to improve my SSIS package performance.


    Thanks Bala Narasimha

    Monday, December 9, 2019 9:16 AM

Answers

  • Hi Bala,

    There are many details to be noticed when designing SSIS package:

    1.  Use the SQL statement instead of loading the table and select the required columns only.
    2.  Avoid using shielded conversions like Aggregate and Sort.
    3.  Use sequence containers in control flow.

    and so on.

    Best Regards,

    Lily




    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by Lily Lii Wednesday, December 11, 2019 9:38 AM
    • Marked as answer by BaluChalla Thursday, December 12, 2019 7:01 AM
    Tuesday, December 10, 2019 3:08 AM

All replies

  • Are these 30 tasks connected in sequence and getting executed in serial manner?

    If they can run independently, you may remove the precedence constraints and make them run in parallel like below (Reference


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Monday, December 9, 2019 9:21 AM
  • I hope you specify in OLEDB Destination Fast Load option

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, December 9, 2019 11:51 AM
  • Hi Bala,

    There are many details to be noticed when designing SSIS package:

    1.  Use the SQL statement instead of loading the table and select the required columns only.
    2.  Avoid using shielded conversions like Aggregate and Sort.
    3.  Use sequence containers in control flow.

    and so on.

    Best Regards,

    Lily




    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by Lily Lii Wednesday, December 11, 2019 9:38 AM
    • Marked as answer by BaluChalla Thursday, December 12, 2019 7:01 AM
    Tuesday, December 10, 2019 3:08 AM
  • Hi Bala,

    Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, December 12, 2019 6:17 AM