- 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)
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 ' 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.