SQL Server Home
Fuzzy lookup triggers SQLDUMPER, even with very small dataset with Run64BitRuntime set to false
9/6/2009 2:53:57 PM
User(s) can reproduce this bug
Fuzzy lookup running small, published package, triggers SQLDUMPER, even with very small dataset, with Run64BitRuntime set to false. Runs fine with it set to true.
SQL Server 2008 - Enterprise Edition
Integration Services (DTS)
Windows Server 2008
Operating System Language
Steps to Reproduce
On a Windows Server 2008 R2 Enterprise instance, running in a VMWare Workstation 6.5 virtual machine on the same operating system running as a host (or perhaps even running on bare metal with no VM--not sure), implement the Lesson26.dtsx package for "Knight's 24-Hour Trainer, Microsoft SQL Server 2008 Integration Services" published by wrox at http://www.wrox.com in support of the excellent book by the same name. Use SQL Server 2008 64 Bit, Enterprise SP1. Modify the connection managers to point at an instance of AdventureWorks2008 database, and the "New Employee" flat file. Run the two scripts to add two tables to the database. At the project level, set Run64BitRuntime to false. Then attempt to execute the package.
On my set-up, the package freezes on the Fuzzy Lookup branch, triggers SQLDumper and generates a message, "SSIS package "Lesson26.dtsx" finished: Canceled. Runs correctly with Run64BitRuntime set to true. In the few seconds prior to the window with SQLDumper loading, a very fast moving 1GB memory spike is consistently seen. Memory utilization with Run64BitRuntime = true is flat through the package execution. (Note however, that sometimes can't set that property to true, as with packages connecting to Excel.) Could this be a memory leak in the 32bit SSIS runtime connected with the Fuzzy Lookup functionality?
Expected the package to complete successfully, just as it does with Run64BitRuntime set to true.
to post a comment.
Please enter a comment.
on 1/24/2013 at 8:07 AM
Since installing SQL server 2012 tools, I can not get any of my SQL server 2008 r2 SSIS packages with fuzzy look-ups to run. I still have SQL 2008 R2 tools loaded and am using the same BIDS(Business Intelligence Development Studio 2008 R2 that I created them with).
The important difference is that it does not work in 32 bit OR 64 bit execution mode. If I run the package on an older machine that doesn't have the 2012 tools installed it still works in either execution mode.
Microsoft, you last responded in 2009, any updates?
on 9/24/2012 at 4:59 PM
Windows 7 Professional
SQL 2008 R2 Developer
Running SSIS package in 32-bit mode, loading data from a flat file, using Fuzzy Lookup (with OLE DB Connection for the lookup) to standardize a column.
I get the "The thread tried to read from or write to a virtual address for which it does not have the appropriate access." error.
I switched to 64-bit and it works.
on 12/14/2011 at 3:32 PM
had been working on a package that needed to pull in excel data when this fuzzy lookup issue bit me
please fix it for SQL Server 2012 RTM
on 9/6/2011 at 1:49 PM
Same problem. I can reproduce the error. Help needed on what else can be tried to make this work.
on 8/16/2011 at 12:05 PM
I'm having the same problem running SSIS in Win7 64 bit and SQL2008R2SP1 a data flow with Excel source and applying to it fuzzy look up it hangs in fuzzy lookup and then finishes without going to destination filling with look up information
on 8/4/2011 at 11:25 AM
I´m running the same problem. My refence table is really small, I can´t execute the package, a dump file is generated (exactly the same error as bellow)
I think is releated to debugging in 32, when i run the package with DTExec (32bits) everything is OK.
In the other hand, i can´t find the way to run my ODBC driver (firebird) in 64bits, so I really need to work in 32bits.
on 7/29/2011 at 2:53 PM
I'm able to reproduce the issue both under SQL 2005 SP3 and SQL 2008 under Windows Vista Business x64. The fact that the same behavior exists in SQL 2005 , means this is an old issue.
on 2/14/2011 at 5:36 AM
I'm experiencing this as well. Two different virtual machines, one is Windows 2008 Server, SQL Server 2008 R2; the other is Win 7, SQL Server 2008 R2
on 1/7/2011 at 8:00 AM
I just thought I would add to the thread that I have also come accross this issue.
Windows 7 Enterprise x64, SQL Server 2008 R2 Developer Edition.
When run in 32 bit mode, Fuzzy Lookup transformation causes SQLDumper.exe and package execution will not continue. Funnily it doesn't seem to fail as such, it just won't proceed beyond the FL. In 64 bit mode it is fine.
It's a real headache because I need to use an Access data source for which (as far as I can tell) there is no 64 bit OLE DB Driver, so I have to run the package in 32 bit mode.
At present this is a show-stopper for me, although I've posted on the forums to see if anyone knows of a workaround/fix.
on 11/9/2010 at 6:11 AM
For me, it does not make a difference whether Run64BitRuntime is true or false, I simply cannot make the Fuzzy Lookup work... See dump file below.
on 11/9/2010 at 6:07 AM
This is the SQL Dump file:
Dump File: SQLDmpr0023.mdmp : C:\Program Files (x86)\Microsoft SQL Server\100\Shared\ErrorDumps\SQLDmpr0023.mdmp
Last Write Time: 9/11/2010 14:55:56
Process Name: DtsDebugHost.exe : C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DtsDebugHost.exe
Process Architecture: x86
Exception Code: 0xC0000005
Exception Information: The thread tried to read from or write to a virtual address for which it does not have the appropriate access.
Heap Information: Not Present
OS Version: 6.0.6002
CLR Version(s): 2.0.50727.4206
Module Name Module Path Module Version
----------- ----------- --------------
DtsDebugHost.exe C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DtsDebugHost.exe 2007.100.1600.22
ntdll.dll C:\Windows\SysWOW64\ntdll.dll 6.0.6002.18005
kernel32.dll C:\Windows\SysWOW64\kernel32.dll 6.0.6002.18005
msvcr80.dll C:\Windows\winsxs\x86_microsoft.vc80.crt_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d08d7da0442a985d\msvcr80.dll 8.0.50727.4053
msvcrt.dll C:\Windows\SysWOW64\msvcrt.dll 7.0.6002.18005
ATL80.dll C:\Windows\winsxs\x86_microsoft.vc80.atl_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d1c738ec43578ea1\ATL80.dll 8.0.50727.4053
advapi32.dll C:\Windows\SysWOW64\advapi32.dll 6.0.6002.18005
rpcrt4.dll C:\Windows\SysWOW64\rpcrt4.dll 6.0.6002.18024
secur32.dll C:\Windows\SysWOW64\secur32.dll 6.0.6002.18051
user32.dll C:\Windows\SysWOW64\user32.dll 6.0.6002.18005
gdi32.dll C:\Windows\SysWOW64\gdi32.dll 6.0.6002.18005
ole32.dll C:\Windows\SysWOW64\ole32.dll 6.0.6002.18277
oleaut32.dll C:\Windows\SysWOW64\oleaut32.dll 6.0.6002.18005
version.dll C:\Windows\System32\version.dll 6.0.6002.18005
psapi.dll C:\Windows\SysWOW64\psapi.dll 6.0.6000.16386
imm32.dll C:\Windows\System32\imm32.dll 6.0.6002.18005
msctf.dll C:\Windows\SysWOW64\msctf.dll 6.0.6002.18005
lpk.dll C:\Windows\SysWOW64\lpk.dll 6.0.6002.18005
usp10.dll C:\Windows\SysWOW64\usp10.dll 1.626.6002.18244
sxs.dll C:\Windows\System32\sxs.dll 6.0.6001.18000
rsaenh.dll C:\Windows\System32\rsaenh.dll 6.0.6002.18005
clbcatq.dll C:\Windows\SysWOW64\clbcatq.dll 2001.12.6931.18000
DTS.dll C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTS.dll 2007.100.2531.0
msvcp80.dll C:\Windows\winsxs\x86_microsoft.vc80.crt_1fc8b3b9a1e18e3b_8.0.50727.4053_none_d08d7da0442a985d\msvcp80.dll 8.0.50727.4053
dtsmsg100.dll C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtsmsg100.dll 2007.100.1600.22
crypt32.dll C:\Windows\System32\crypt32.dll 6.0.6002.18005
msasn1.dll C:\Windows\System32\msasn1.dll 6.0.6002.18106
userenv.dll C:\Windows\System32\userenv.dll 6.0.6002.18005
msxml6.dll C:\Windows\System32\msxml6.dll 6.20.5002.0
shlwapi.dll C:\Windows\SysWOW64\shlwapi.dll 6.0.6002.18005
comctl32.dll C:\Windows\winsxs\x86_microsoft.windows.common-controls_6595b64144ccf1df_6.0.6002.18305_none_5cb72f2a088b0ed3\comctl32.dll 6.10.6002.18305
mscoree.dll C:\Windows\System32\mscoree.dll 4.0.31106.0
mscoreei.dll C:\Windows\Microsoft.NET\Framework\v4.0.30319\mscoreei.dll 4.0.30319.1
mscorwks.dll C:\Windows\Microsoft.NET\Framework\v2.0.50727\mscorwks.dll 2.0.50727.4206
shell32.dll C:\Windows\SysWOW64\shell32.dll 6.0.6002.18287
mscorlib.ni.dll C:\Windows\assembly\NativeImages_v2.0.50727_32\mscorlib\98bbdd8c400493ad228b8283665cc9da\mscorlib.ni.dll 2.0.50727.4206
System.ni.dll C:\Windows\assembly\NativeImages_v2.0.50727_32\System\ed6ae2749d12c4729ee43ff339de4bb8\System.ni.dll 2.0.50727.4205
Microsoft.SqlServer.ManagedDTS.ni.dll C:\Windows\assembly\NativeImages_v2.0.50727_32\Microsoft.SqlServer#\8df3aa30679e202f924e537301ff91e0\Microsoft.SqlServer.ManagedDTS.ni.dll 10.0.1600.22
Microsoft.SqlServer.DTSRuntimeWrap.ni.dll C:\Windows\assembly\NativeImages_v2.0.50727_32\Microsoft.SqlServer#\39094c66be328ea4393df4715bda7c86\Microsoft.SqlServer.DTSRuntimeWrap.ni.dll 2007.100.1600.22
Microsoft.SqlServer.ManagedConnections.ni.dll C:\Windows\assembly\NativeImages_v2.0.50727_32\Microsoft.SqlServer#\1589d1129bf35f5eace909782ef03ab0\Microsoft.SqlServer.ManagedConnections.ni.dll 10.0.1600.22
Microsoft.SqlServer.SqlTDiagM.ni.dll C:\Windows\assembly\NativeImages_v2.0.50727_32\Microsoft.SqlServer#\9854a7c812c2e811b213a192a6ebada4\Microsoft.SqlServer.SqlTDiagM.ni.dll 10.50.1447.4
Microsoft.SqlServer.PipelineHost.ni.dll C:\Windows\assembly\NativeImages_v2.0.50727_32\Microsoft.SqlServer#\128dff83a4fad8a147d3c8e11c6b48e7\Microsoft.SqlServer.PipelineHost.ni.dll 10.0.1600.22
DtsConn.dll C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DtsConn.dll 2007.100.1600.22
mscorjit.dll C:\Windows\Microsoft.NET\Framework\v2.0.50727\mscorjit.dll 2.0.50727.4016
DTSPipeline.dll C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTSPipeline.dll 2007.100.2531.0
Microsoft.SqlServer.PipelineXML.ni.dll C:\Windows\assembly\NativeImages_v2.0.50727_32\Microsoft.SqlServer#\96103b48b5047420f10d2629491e891d\Microsoft.SqlServer.PipelineXML.ni.dll 10.0.1600.22
Microsoft.SqlServer.SqlTDiagM.dll C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlTDiagM\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlTDiagM.dll 10.50.1447.4
System.Data.dll C:\Windows\assembly\GAC_32\System.Data\126.96.36.199__b77a5c561934e089\System.Data.dll 2.0.50727.4016
ws2_32.dll C:\Windows\SysWOW64\ws2_32.dll 6.0.6001.18000
nsi.dll C:\Windows\SysWOW64\nsi.dll 6.0.6001.18000
Microsoft.SqlServer.Diagnostics.STrace.dll C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Diagnostics.STrace\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Diagnostics.STrace.dll 10.50.1447.4
System.dll C:\Windows\assembly\GAC_MSIL\System\188.8.131.52__b77a5c561934e089\System.dll 2.0.50727.4205
System.Configuration.dll C:\Windows\assembly\GAC_MSIL\System.Configuration\184.108.40.206__b03f5f7f11d50a3a\System.Configuration.dll 2.0.50727.4016
System.Xml.dll C:\Windows\assembly\GAC_MSIL\System.Xml\220.127.116.11__b77a5c561934e089\System.Xml.dll 2.0.50727.4016
Microsoft.SqlServer.DTSPipelineWrap.ni.dll C:\Windows\assembly\NativeImages_v2.0.50727_32\Microsoft.SqlServer#\5c763078f7880bde29c77c9a57bcb415\Microsoft.SqlServer.DTSPipelineWrap.ni.dll 2007.100.1600.22
Microsoft.SqlServer.DtsMsg.ni.dll C:\Windows\assembly\NativeImages_v2.0.50727_32\Microsoft.SqlServer#\2d829ed32f54bc18f1fab4a4bb0e0fbc\Microsoft.SqlServer.DtsMsg.ni.dll 10.0.1600.22
Microsoft.SqlServer.msxml6_interop.ni.dll C:\Windows\assembly\NativeImages_v2.0.50727_32\Microsoft.SqlServer#\a49c62442054fb83608dd49c5f013064\Microsoft.SqlServer.msxml6_interop.ni.dll 18.104.22.168
Microsoft.SqlServer.DTSRuntimeWrap.dll C:\Windows\assembly\GAC_32\Microsoft.SqlServer.DTSRuntimeWrap\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.DTSRuntimeWrap.dll 2007.100.1600.22
instapi10.dll C:\Program Files (x86)\Microsoft SQL Server\100\Shared\instapi10.dll 2007.100.1600.22
sqlboot.dll C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlboot.dll 2007.100.1600.22
OleDbSrc.dll C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents\OleDbSrc.dll 2007.100.1600.22
dtsmsg100.rll C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\Resources\1033\dtsmsg100.rll 2007.100.1600.22
TxDerived.dll C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents\TxDerived.dll 2007.100.1600.22
TxBestMatch.dll C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents\TxBestMatch.dll 2007.100.1600.22
oledb32.dll C:\Program Files (x86)\Common Files\System\Ole DB\oledb32.dll 6.0.6001.18000
msdart.dll C:\Windows\System32\msdart.dll 6.0.6001.18000
comctl32.dll C:\Windows\winsxs\x86_microsoft.windows.common-controls_6595b64144ccf1df_5.82.6002.18305_none_88f3a38569c2c436\comctl32.dll 5.82.6002.18305
comdlg32.dll C:\Windows\SysWOW64\comdlg32.dll 6.0.6002.18005
wininet.dll C:\Windows\SysWOW64\wininet.dll 8.0.6001.18975
normaliz.dll C:\Windows\SysWOW64\normaliz.dll 6.0.6000.16386
urlmon.dll C:\Windows\SysWOW64\urlmon.dll 8.0.6001.18975
iertutil.dll C:\Windows\SysWOW64\iertutil.dll 8.0.6001.18975
oledb32r.dll C:\Program Files (x86)\Common Files\System\Ole DB\oledb32r.dll 6.0.6000.16386
comsvcs.dll C:\Windows\System32\comsvcs.dll 2001.12.6932.18005
atl.dll C:\Windows\System32\atl.dll 3.5.2284.2
sqlncli10.dll C:\Windows\SysWOW64\sqlncli10.dll 2007.100.2531.0
netapi32.dll C:\Windows\SysWOW64\netapi32.dll 6.0.6002.18005
SQLNCLIR10.RLL C:\Windows\SysWOW64\1033\SQLNCLIR10.RLL 2007.100.1600.22
credssp.dll C:\Windows\System32\credssp.dll 6.0.6001.18000
schannel.dll C:\Windows\SysWOW64\schannel.dll 6.0.6002.18290
kerberos.dll C:\Windows\SysWOW64\kerberos.dll 6.0.6002.18051
cryptdll.dll C:\Windows\System32\cryptdll.dll 6.0.6001.18000
msv1_0.dll C:\Windows\SysWOW64\msv1_0.dll 6.0.6002.18111
ntdsapi.dll C:\Windows\System32\ntdsapi.dll 6.0.6001.18000
dnsapi.dll C:\Windows\System32\dnsapi.dll 6.0.6002.18005
Wldap32.dll C:\Windows\SysWOW64\Wldap32.dll 6.0.6002.18005
security.dll C:\Windows\System32\security.dll 6.0.6000.16386
mswsock.dll C:\Windows\System32\mswsock.dll 6.0.6002.18005
WSHTCPIP.DLL C:\Windows\System32\WSHTCPIP.DLL 6.0.6001.18000
wship6.dll C:\Windows\System32\wship6.dll 6.0.6001.18000
nlaapi.dll C:\Windows\System32\nlaapi.dll 6.0.6001.18000
IPHLPAPI.DLL C:\Windows\System32\IPHLPAPI.DLL 6.0.6002.18005
dhcpcsvc.dll C:\Windows\System32\dhcpcsvc.dll 6.0.6002.18005
winnsi.dll C:\Windows\System32\winnsi.dll 6.0.6001.18000
dhcpcsvc6.DLL C:\Windows\System32\dhcpcsvc6.DLL 6.0.6002.18005
NapiNSP.dll C:\Windows\System32\NapiNSP.dll 6.0.6001.18000
winrnr.dll C:\Windows\System32\winrnr.dll 6.0.6002.18005
rasadhlp.dll C:\Windows\System32\rasadhlp.dll 6.0.6000.16386
dssenh.dll C:\Windows\System32\dssenh.dll 6.0.6001.18000
ncrypt.dll C:\Windows\System32\ncrypt.dll 6.0.6002.18005
bcrypt.dll C:\Windows\System32\bcrypt.dll 6.0.6002.18005
apphelp.dll C:\Windows\System32\apphelp.dll 6.0.6002.18005
Cees van Diermen
on 11/1/2010 at 5:12 AM
Just experienced the same using sql2008 r2 developer edition. Package simply stops and generates dumpfile.
on 8/30/2010 at 4:58 PM
Thanks a lot for your feedback. We have so far not been able to repro this issue in-house. If you are still encountering this, can you attach the crash dump to this bug?
on 8/3/2010 at 3:38 AM
Cannot execute fuzzy lookups within package that have access as datasource. We dont have 64bits driver for read from Access or Excel files, and the fuzzy lookup don´t run in 32bits.
on 7/7/2010 at 6:54 AM
Not a solution, but a workaround if you need Excel import and Fuzzy logic in the same package: Running the Package on command line interface will work (32Bit DTExec.exe).
on 3/15/2010 at 11:24 AM
Seeing this as well. Win7x64 +Visual Studio 2005 (BIDS) V 8.0.50727.4039 +SQL Server 2005 Developer Edition
Debugging in visual studio I believe has Run64BitRuntime set to false, but I am unable to tell. Cannot find an option in BIDS to change this. :/
on 3/3/2010 at 9:52 AM
Reproduced here too. Sql 2008 SP1 on Windows 7 Enterprise x64 - SSIS - very simple fuzzy lookup dataflow.
Goes yellow up until fuzzy lookup, "[SSIS.Pipeline] Information: Execute phase is beginning." then SQLDUMPER kicks in and package execution ends.
This seems to only occur with debug mode Run64Runtime=false
on 12/9/2009 at 8:30 AM
same issue on Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)
on 11/22/2009 at 5:39 PM
I ran into the same problem. Simple Fuzzy Lookup which hangs: doesn't pass any records downstream and remains yelllow. No error messages, it just hangs. And the log says that the package was cancelled (even though the Data Flow on the Control Flow tab, and the transforms on the Data Flow up until and including the Fuzzy Lookup are all still yellow. Doesn't seem to be anything unusual about the Fuzzy Lookup transform.
I have gotten other Fuzzy Lookups to work on the same server without this bug.
on 10/1/2009 at 2:20 PM
I get the same issue running on Vista Business Edition (x64).
on 9/24/2009 at 1:22 PM
Thank you for your submission. We will be reviewing your information and providing you feedback on our findings and progress. Thanks for your interest and support of SSIS.
to post a workaround.
Please enter a workaround.
on 3/30/2012 at 1:47 PM
I had faced the same issue and in my case the fix/workaround was simply in changing the Run64BitRuntime property of the SSIS project from FALSE to TRUE (I was on a 64 Bit machine).
on 11/30/2011 at 2:15 PM
It is possible to load data from Excel or Access and run a Fuzzy Lookup transformation on it in the same control flow. You will need to create two packages: one that imports the data via Excel/Access that will run in 32-bit mode, and another that executes the Fuzzy Lookup transformation(s) in 64-bit mode. You will call the packages using the Execute Process Control Flow Task, with the arguments set to call the appropriate version of dtexec (32-bit is, by default, here: C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn, 64-bit is here: C:\Program Files\Microsoft SQL Server\100\DTS\Binn.
My project (2008 R2) requires an Access source adapter and a Fuzzy Lookup. I import and stage the data to SQL in the main control flow package, which runs in 32-bit mode. When the import is done, an Execute Process Task runs a package in 64-bit mode. This package runs the pieces of the extract that require Fuzzy Lookups. The variables that I need in the 64-bit package are passed down via the arguments in the EPT.
It's not the prettiest solution, but it allows all components to reside in the same control flow.
on 1/31/2011 at 2:17 AM
I also face the same problem. Simple Fuzzy Lookup which hangs: doesn't pass any records and remains yelllow. There is no error messages, it.when you see the log ; the package was cancelled (even though the Data Flow on the Control Flow tab, and the transforms on the Data Flow up until and including the Fuzzy Lookup are all still yellow. Doesn't seem to be anything unusual about the Fuzzy Lookup transform.
© 2013 Microsoft