While debugging an SSIS package that loops over thousands of files, execution hangs with no errors and no failed tasks. Slightly simplified, the package does this:

For Each File                       (a directory full of pdf files, each ranging from 800 -
                                        2000 pages and 3 - 8MB filesize)
    *   Script Task                 (get file information)
    *   File System Task            (clear out temp folder)
    *   Execute Process Task        (use pdftk to break file into individual pages)
    *   For Each File               (each page in temp folder, 40 - 100KB each)
        *   Script Task             (parse pdf file)
        *   Execute Process Task    (pdftk to concatenate like pages)
        *   Execute SQL Task        (insert one row into staging table using OPENROWSET)
        *   File System Task        (delete inserted file)
        *   Script Task             (set variables for next iteration)
    *   Execute SQL Task            (execute proc to merge staging into prod)
    *   File System Task            (rename completed file)

When I execute the package (debug mode in BIDS), it runs as expected through the first file and well into the second, but eventually hangs between iterations of the inner loop. When it hangs, all tasks are green, and the two loop containers are yellow. The output window does generate any messages, and going through the process tab, each task has a matching number of starts and stops except for the two loops and the package itself.

My first thought was that I'd exceeded an iteration limit for the For Each File enumerator, but I cannot find any documentation for such a limit. What else can I check to determine why SSIS stops running?

  • 1
    Do you get the same results if you run the package outside of the debugger? dtexec.exe /file MyPackage.dtsx How does your computers stats look when it appears to be hung? Is the dtsdebug process eating all of your memory or 2GB or whatever the upper bound is? Which of the inner steps does it hang on? My prime assumption would be either your custom script task or the pdftk process. – billinkc Jul 16 '13 at 21:31
  • 1
    Are you positive that your script task has actually succeeded, and hasn't thrown up a little exception window waiting to be acknowledged? I would put some error handling and logging in there so I can see excatly where its at. – Liam Confrey Jul 16 '13 at 21:38
  • Did it complete via dtexec? – billinkc Jul 18 '13 at 14:40

This appears to be an issue with the Visual Studio / BIDS client hitting memory boundaries, not with SSIS itself. While the package is running in debug mode in BIDS, the devenv.exe process can be observed to continually occupy additional memory. While the number of files processed before hanging is inconsistent, the package did, on one occasion, process nearly 9000 files before Visual Studio threw an out of memory exception.

Running the package with dtexec or through SQL Agent appears to resolve the issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.