SSIS - package working only when there is breakpoint

SUNGHOON Kritesh 1 Reputation point
2021-09-03T14:21:23.33+00:00

I have a SSIS package which contains different sequence container, the problem is when I execute the whole package, it executes first 2-3 containers then gets Index out of bound error in next container (In a script task inside a data flow task).

But when I put a debug point inside the script component, it executes fine and throws no error. Even if I execute each container separately, then there is no error. I am totally confused with this, any idea?

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,487 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,489 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Michael Taylor 50,026 Reputation points
    2021-09-03T15:30:30.747+00:00

    This is an indication of a race condition or threading issue. My first thought is that you have some variables scoped to the package and the containers are using them but you are setting the variables inside the containers and hence any that run in parallel are stomping over others that also run in parallel. You should verify that all variables you are writing to inside each of your containers is scoped only to that container. A common place of failure when using script tasks is taking an existing HTTP connection that is configured at the package level and trying to use it in a script but modifying a property on the connection. Since the connection is shared it causes other tasks that may be using the same connection to collide.

    You also need to look at the execution log of SSIS. It will first make a pass through to see what tasks to run followed by executing them. Enable logging and you'll see all this info so you can figure out where it is potentially failing. If all else fails add some extra logging into your code including any script tasks.

    0 comments No comments

  2. ZoeHui-MSFT 34,581 Reputation points
    2021-09-06T07:22:55.637+00:00

    Hi @SUNGHOON Kritesh ,

    SQL Server Integration Services includes log providers that you can use to implement logging in packages, containers, and tasks. With logging, you can capture run-time information about a package, helping you audit and troubleshoot a package every time it is run.

    Enable Logging for Package Execution which will help us to troubleshoot the issue.

    integration-services-ssis-logging

    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

    0 comments No comments

  3. SUNGHOON Kritesh 1 Reputation point
    2021-09-06T07:28:06.453+00:00

    Thanks, @Michael Taylor and @ZoeHui-MSFT

    I will keep that in mind. However, I've recreated the script component, it's running fine.

    I am performing some tests right now, in case I re-encounter this issue, I will let you guys know.

    Thanks a lot,
    Kritesh

    0 comments No comments