Fuzzy Lookup - Not enough storage is available to complete this operation - Works in Visual Studio, Fails with DTExec
I have a Fuzzy Lookup operation that is referencing a very large view, which is a subset of a larger table. When running the package from Visual Studio, execution succeeds without a hitch. When running the package from DTExec or C# code it fails with the following error: 0x8007000E : Not enough storage is available to complete this operation. Why would it run successfully in Visual Studio yet fail in any sort of production environment? Is there some package configuration setting I'm missing? Any help is appreciated!
October 16th, 2012 5:52pm
what is the edition of your SQL server? Fuzzy logic is an Enterprise feature. | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA Hate to mislead others, if I'm wrong slap me. Thanks!
October 16th, 2012 6:58pm
SQL Server 2008 - I'm not sure of the exact license but we have fuzzy logic capabilities in production now, so I'm sure that's not the issue.
October 16th, 2012 8:29pm
Are you running it in Visual Studio against copy of production data or some sample or subset of data? It is probable that is is failing with out of memory error - please observe memory usage on the prod server during execution.
October 17th, 2012 12:23am
Thanks for the response. I suppose my use of the word "production" was a poor choice. Basically any time I execute the package outside of Visual Studio I get failure - even with the same database on the same machine with similar load conditions. I ran the following test today: 1) Open Visual Studio and execute the package via right click -> execute package. Package runs successfully. 2) Open cmd prompt and execute the package via DTExec.exe. Package fails with the error mentioned above. It's the same package, on the same server, using the same data, executed different ways. The memory usage on the server is high when doing the test, that's for certain - it's a very large table (mind you we're only using 2 columns for the fuzzy lookup), however it's just weird that it succeeds every time in VS, yet fails every time outside of VS.
October 17th, 2012 12:47am
Hi j.nieuwhof, What version of Dtexec you are using? If you are using 64 bit dtexec, please try to use 32 bit dtexec to run your package, for more information about SSIS and Memory, please see: http://sql-ramblings.blogspot.com/2010/12/ssis-and-memory.html Here is a similar thread, please refer to: http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/77ecbdba-95b7-4e0b-a0fd-5cb6ebdcc5b3 Thanks, Eileen Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
October 20th, 2012 11:17pm