SSIS - package working only when there is breakpoint

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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