Solved

SSIS - "Not enough storage is available to complete this operation" when executing Directory.GetFiles method in a ScriptTask

Posted on 2013-11-07
13
3,115 Views
Last Modified: 2016-02-11
My environment:

- Visual Studio 2008 SP1

- Sql Server 2008 x64

- Developement machine: Windows 8.1 x64 all updates installed - i7quad - 8GB Memory

- Sql Server production machine: Cluster (A/P): 2x Windows 2008 R2 x64 - 32GB Memory

This problem is driving me crazy.
Some production SSIS jobs access an AS400 folder (QDLS) for their processing.
In particular, they call the 7zip executable to create an archive with the files in a QDLS sub-folder and send it via ftp.
No problem so far. Everything works correctly.

After processing is complete (after the archive has been sent), the job should move the processed files to a backup folder.
So, within a "Script Task" I put a C# code that deals with this.


But after many months of executions correct, the jobs are now in error at the point: Directory.GetFiles("path_to_qdls");

I can replicate the error by debugging the script from my pc:

Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Exception: \\192.168.x.x\qdls\ ---> System.IO.IOException: Not enough storage is available to complete this operation.

   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
   at System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption)
   at System.IO.Directory.GetFiles(String path, String searchPattern, SearchOption searchOption)
   at System.IO.Directory.GetFiles(String path)
   at ST_76f7dac7d52b44b6b988a5c517655f1d.csproj.ScriptMain.Main() in C:\Users\x\AppData\Local\Temp\SSIS\1792c6065afc40ba883b89b83583cb85\ScriptMain.cs:line 61
   --- End of inner exception stack trace ---
   at ST_76f7dac7d52b44b6b988a5c517655f1d.csproj.ScriptMain.Main() in C:\Users\x\AppData\Local\Temp\SSIS\1792c6065afc40ba883b89b83583cb85\ScriptMain.cs:line 80
   --- End of inner exception stack trace ---
   at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
Task failed: Script Task
Warning: 0x80019002 at TEST: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "TEST.dtsx" finished: Failure.
The program '[7548] TEST.dtsx: DTS' has exited with code 0 (0x0).


Does anybody have an idea of what is the cause of this strange behavior (strange to me)?

Please note that:

- Any folder I tried, other than QDLS, on the AS400 works fine.

- QDLS folder (even if it is not really a "windows share") has full permission for all users (something equivalent to "everyone - full control)

- The packages are executed in production with an ad-hoc user (named "SqlAg"), that is used for all the jobs.

- The problem can be replicated by debugging the package in VS2008, so it seems to me not to be a problem related to the executing user.

- The package executes a 7zip command-line, which takes files from the QDLS folder. This operation works without problems.

- It seems the problem is related to the directory listing only: I created a test dtsx with only a ScriptTask inside, and the script with only the Directory.GetFiles() line....

I have already tried to:

- Change Directory.GetFiles() with DirectoryInfo.GetFiles()

- Use the "dir" command wrapped into a method, instead of the above two lines.

- Change the executing user of the jobs, by creating a proxy to another (more globally privileged) user.
0
Comment
Question by:jonnidip
  • 7
  • 5
13 Comments
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 39630456
This error is thrown when an attempt to allocate memory in .NET fails.

GetFiles returns an array of String. This is probably occurring when allocating memory for the results array.

Maybe GetFiles is returning a very large (larger than in the past) result set.  How may files are there in QDLS?  Has that changed recently (or just grown continuously over time) where now there are a lot more files than in the past?

- Gary Patterson
0
 
LVL 13

Author Comment

by:jonnidip
ID: 39630475
In this particular case the qdls sub-folder is empty.
These folders I am working on usually contain from 0 to 10 files, not more.

Thank you for your help.
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 39630591
If volumes of files are small, then it may be that some specialized resource is exhausted, like file handles.

Here's a good troubleshooter:

http://blogs.technet.com/b/abizerh/archive/2009/07/12/troubleshooting-the-error-not-enough-storage-is-available-to-complete-this-operation.aspx

- Gary Patterson
0
 
LVL 13

Author Comment

by:jonnidip
ID: 39631072
Thank you for pointing me to that article. I have already read that, but I think I am in a different situation than the ones shown.
That article shows different solutions for logon-related problems. But if it was a logon issue, then why does it fail with my user too?
At the beginning of the article it is said that the problem seemed to be solved when the machine has been rebooted. Well, I switched the active cluster note to the other server, but it did not solve my problem (the other node has been rebooted previously).

Then, I think that if it was a problem of handles, then I should have seen an abnormal memory consumption, but it did not happen. In addition, the error occurs immediately when hitting the "GetFiles" method. There is no "filling time".
Then, I think that a file-handle-exausted related error may appear when the machine (or the process) has been running for a while, but I tried with a fresh-boot of the server and I tried locally on my PC.

I don't want to lead you to a possible bad way, but my opinion is that this could be a qdls problem. Qdls seems to be a special kind of folder in AS400 and I really don't know anything about it. I asked my AS400 colleague if it would be possible to save the files to another folder, but it seems to be quite difficult at the moment.


Any idea?
0
 
LVL 34

Accepted Solution

by:
Gary Patterson earned 500 total points
ID: 39631591
I've been troubleshooting AS/400-to-Windows problems since about 1990, and AS/400-to -DOS problems before that.  This isn't exactly unfamiliar territory.

This is a Windows .NET method call that is failing and throwing an error.  

It is possible that it is something that went wrong on AS/400 and caused it?  Maybe, but not likely at the volumes you described.

What has changed recently?  Did they upgrade or install PTFs on the AS/400?  Did anything change on the Windows side?

Directory.GetFiles, when used against a remote system, ends up causing a SMB/CIFS transaction to be initiated.  A connection is established, authentication is performed, and messages are exchanged.  

Ultimately, a CIFS message is sent to request directory information from the remote system (the AS/400 in this case).  

On the AS/400, Windows file sharing requests are received and handled by a service called "NetServer".

Once connected and authenticated, NetServer will then request a list of files from the QDLS file system using the appropriate AS/400 API, and formats and sends the list back to the Windows requester - again using SMB/CIFS protocol messages.

The Windows box, according to you, is executing Directory.GetFiles() (a .NET method), and failing with a the .NET exception "System.IO.IOException: Not enough storage is available to complete this operation".

That happens when GetFiles() attempts to allocate Windows resources - and as you can see, there is a lot going on in Windows - connection allocation, authentication, redirection, communications - plenty of places for something to go wrong.

System.IO.IOException: Not enough storage is available to complete this operation is not an AS/400 message, and it isn't a SMB/CIFS message, as far as I know:

http://msdn.microsoft.com/en-us/library/ee441884.aspx

I just want to eliminate a runaway program on Windows before we start doing more complex troubleshooting.

QDLS is "special" in that it is designed for compatibility with DOS naming conventions - ("eight dot three").  It is old, and relatively slow, and has strict file naming restrictions.  It won't hurt anything to try to switch over to a different file system on the AS/400.  That should be very easy.  If your admin or developer doesn't know how, I'd be happy to explain.

I don't think you necessarily have a "login problem".

I do think you have some sort of resource allocation issue in Windows that is preventing GetFiles() from working properly, and I think you need to solve that problem.

As far as the "Not Enough Storage" article goes:  

This is a general purpose troubleshooter for anything that results in a "Not enough storage" error.  Not just for login issues.

So please check your handle counts when the error happens, and post what you see here.  

Open task manager
Go to the Process tab (show processes from  other users)
Add the "Handles" column.
Sort by Handles from big to little.

- Gary Patterson
0
 
LVL 13

Author Comment

by:jonnidip
ID: 39631886
Thank you Gary. Your comment is very professional.
I will try to investigate more, basing on your suggestions.

Thank you.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 27

Expert Comment

by:tliotta
ID: 39633347
- Any folder I tried, other than QDLS, on the AS400 works fine.

/QDLS really shouldn't be used by anything later then Windows 2000. If using one of the more modern file systems works, you'll save yourself future problems by switching. The /QDLS file system was last updated to be compatible with OS/2 and with Windows 2000.

Tom
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 39633448
Just went back and re-read question from the top.

- Any folder I tried, other than QDLS, on the AS400 works fine.

- QDLS folder (even if it is not really a "windows share") has full permission for all users (something equivalent to "everyone - full control)


I think I misunderstood part of this question.

If you want to access QDLS remotely using "//ip.address/qdls/", then qdls MUST be shared in NetServer.  That is a basic pre-requisite.  NetServer doesn't "know" about a file system or folder unless it is shared (or if it is a child folder under a share).

Alternately, if there is a root share set up, then you could use "//ip.address/root_share_name/qdls"

But this will never work if QDLS isn't either shared directly, or found other another share.

- Gary
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 39633462
Sharing QDLS is easy.  Here is the link to the IBM Info Center:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Frzahl%2Frzahlnetfileshare.htm

- Gary
0
 
LVL 13

Author Comment

by:jonnidip
ID: 39633478
The qdls folder IS somewhat "shared", I can access it with \\192.168.x.x\qdls.
Please note that directory listing from Windows Explorer or .NET "Directory.GetFiles()" or DOS "dir" works fine.
The problem exists only inside Script Task of SSIS jobs...

I still haven't tried all the suggestions above, because I am busy with other works.

Regards.
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 39633886
No such thing as "sort of shared".  There is either a share configured, or not.  If it is, it is accessible by authorized users if NetServer is running.  

If you can access it via \\192.168.x.x\qdls then it is shared.

What version of Windows and what AS/400 OS version are you using?

- Gary
0
 
LVL 13

Author Closing Comment

by:jonnidip
ID: 39677906
Gary, yours is a very very complete answer.
I tried your suggestions and ended up switching to a different folder (not qdls) on the AS400 side.

Thank you.
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 39677919
Thanks for following up.  

Never tried to use QDLS from SSIS - I'm curious why there is an issue.  I may give it a shot on our dev system the next time I have to test something in SSIS.

Glad you got it worked out.

- Gary
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Creating Alerts in sql sever 2 13
fomat Json objects 6 18
XML & .net 5 21
Convert SP in a format for debugging 7 13
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now