I am currently working on a project which needs to load over a 1000 xml files. The files are stored across 10 subfolders. I am using a foreach loop with a file enumerator, which is configured at the top of the folder structure and traverses the subfolders. This loops through the files, load the data and then moves the file to another folder. The package executes fine for a few 100 files but then hangs; this happens after a different number of processed files each time the package is run. While trying to resolve the problem we ran performance counters and noticed that the number open handles increased significantly just about the time Dtexec looked like it had hanged and DTexec also then started taking a lot of the cpu processing time. Has one else come across similar situations
SSIS Foreach loop stress testing problem.
Hot Topic
- Slow performance calculated members
- Asynchronous Outputs on Script Component Best practice
- Noob questions about Reporting Services
- Groupings in Matrix Layout?
- Datasource Reader - Name for output column is blank.
- Accessing AS400 Data
- Getting “EXECUTE permission denied on object 'sp_start_job'” from activation procedure.
- Error while importing Text file using Import Export Wizard
- creating a server instance on SQL server 2005 enterprise
- string datatype
SQL Server
- Data Source passwords in Config Files
- Optimizing LIKE OR Selects
- Query the Full-Text Words List?
- Article - delete all data property
- derived column - column removal
- Cannot Register with External Access
- Display Row Data As Cols
- Interfacing with SQL Server 2005 EXPRESS
- Database Diagram error on SQL 2005
- Error at parse query
SSIS Foreach loop stress testing problem.
Ricardo RWS
Steve,
We are aware of an issue with looping and handles being leaked and it should be resolved in SP1 (if it is the same problem, which seems highly likely from your description).
Sorry, I don't know when SP1 will be available. However, as a workaround you could create a separate package and run it out of process. Not pretty but it will solve the problem until you can get SP1.
Thanks,
Matt
MikeCampbell
Not sure if this will help, but perhaps try increasing the number of file handles.
Here is how to fix the “No More File Handles Left” for Windows/XP
Left Click START
Left Click RUN
type COMMAND to open the DOS terminal windo and get to DOS prompt
at the C:> prompt - type "cd windowssystems32" without the quotes
you should now be at
c:WindowsSystem32>
type "edit" without the quotes and you should get to the Dos editor
Click "File" and "Open"
find the file called "CONFIG.NT" and press OK (you will have to scroll
down to find it but it should be there)
With the file open scroll all the way down to the last line. Most of the lines
are "remed" out.
Change "files=40" to "files=200" (without the quotes)
Left Click (at upper left of DOS Window) "File"
Left Click "Save" and the Config.nt file is now saved.
Left Click "Exit" to get out of the DOS editor
at C:> promt type "Exit" to close the DOS terminal window
Now you must reboot your computer
Ryn
Hi Andrew
I forgot to say that I am getting this probelm with windows 2003 server. I still tried out the steps you suggested, but I can't see a config.nt file. Also my config.sys file is empty, also I thought these were no-longer used Or is there another place to set the number of file handles.
Regards
Steve