Package design considerations - break up SQL task into multiple other tasks? RRS feed

  • Question

  • At the end of the package Im building, there is a step that involves a updating a single table.  In general, I need to do several checks against a few of its fields, then finally update a field on this table.  I could do this one of several ways, but in general, does anyone have a "rule of thumb" on when to break up some block of logic into more than one task?
    For example instead of 1 sql task, break it up into 2 or 3 separate tasks, including expression and sql tasks etc.

    • Edited by shiftbit Wednesday, January 31, 2018 7:40 PM dfgdfg
    Wednesday, January 31, 2018 7:39 PM

All replies

  • Not knowing the details of what you are trying to accomplish, it's kind of hard to say. If you can test the destination table data in one SQL query then do that in one task then if the test passes, do the table update in a subsequent dataflow or SQL task. You can put the results of the initial test into a variable and use that to determine the whether the next task should be executed or not.
    • Proposed as answer by Pirlo Zhang Friday, February 2, 2018 8:23 AM
    Wednesday, January 31, 2018 7:50 PM
  • Hi shiftbit,

    Everything depend on your exact business logic and performance requirements.

    If you are working in same database, you could wrap all T-SQL script into a stored procedure, then call it from Execute SQL Task, in this way, the performance is better as they are pre-compiled on the database.

    It's also a choice to split one task to multiple tasks, in my opinion, the advantage of this method is security isolation. If the package encounters error, it's easy to find which task caused the issue and easy to maintain. And also multiple tasks can achieve more complex business logic.

    Check if this helps.


    Pirlo Zhang

    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 Pirlo Zhang Friday, February 2, 2018 8:23 AM
    Thursday, February 1, 2018 2:42 AM