Troubleshooting SSIS packages that fail randomly when reading from MongoDB

SSIS PowerPack Troubleshooting
, ,
1

Problem

An SSIS job that reads several MongoDB views (for example, 5 views processed by separate packages or Data Flow Tasks) fails randomly:

  1. On some runs, most of the views fail (e.g., 4 of 5).
  2. On other runs, most of them succeed (e.g., 4 of 5).
  3. The only consistent result is that the job fails almost every day, even though there are no changes to the views or the data.

Cause

Random failures like this usually point to timing and concurrency, not to the views themselves. Common causes are:

  1. Connection timeout is too low. When several MongoDB queries start at once, some connections take longer than the timeout to open or return the first batch of data, so they fail.
  2. Too many tasks run in parallel. With MaxConcurrentExecutables set to -1 (the default, meaning number of processors + 2), SSIS runs as many tasks and child packages at the same time as it can. As a result, the MongoDB server receives many simultaneous connections and queries.
  3. Validation opens connections too early. If validation is not delayed, every Data Flow and source tries to connect to MongoDB at the same moment while the package starts, which adds to the connection spike.

Solution

Apply the following changes in the parent package and in every child package that reads from MongoDB.

Method 1: Increase the connection timeout

  1. In Connection Managers, double-click the MongoDB connection.
  2. Increase the connection timeout (and the command/request timeout, if available) to a value that fits your slowest view.
    image
    image676×635 33.1 KB

Method 2: Limit concurrency

  1. Click an empty area of the Control Flow to select the package.

  2. Open the Properties window (F4).

  3. Change MaxConcurrentExecutables from -1 to 1, so tasks and child packages run one at a time.

    image
    image379×463 14.1 KB

  4. If the total run time is too long, try a small value such as 2 or 3 instead, and test again.

Method 3: Enable Delay Validation

  1. Select the MongoDB connection manager, open the Properties window, and set DelayValidation to True.

  2. Select each Data Flow Task that reads from MongoDB and set DelayValidation to True.

    image
    image376×374 10.9 KB

  3. Do the same on the source component level if the property is available there.

  4. This way, the connection is opened only when the task actually runs, not during package validation.

Deploy and test

  1. Repeat the changes above in all child packages, not only in the parent package.
  2. Deploy the updated project to the server (for example, the staging server first).
  3. Run the job several times and confirm it completes successfully every time before moving the change to production.

Best practice

To avoid recurring issues:

  1. Do not run many MongoDB sources in parallel against the same server unless it can handle the load.
  2. Set a timeout that fits your slowest query, not the average one.
  3. Use DelayValidation on connections and Data Flows that depend on external systems.
  4. Apply these settings to child packages too, since they have their own properties and do not inherit them from the parent.
  5. If the issue persists, enable logging on the component and compare a failed run against a successful one to see which step times out.

Need more help?

If the issue persists, contact ZappySys Support: