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?