SSDT crashes when SSIS package tries to access MS-Access database

1

We want to migrate SSIS packages from 2008 to 2014.

  1. We have Windows10 + Visual Studio Proff 2015 + SSDT 2015: When we try access a MS-Access database (particularly *.accdb) file from DataFlow tax. As soon as we add the *.accdb file into connectionManager and clicks ok. It starts validating the OLEDB connection, and then Visual Studio gets crashed.
  2. Same is happening with Windows10 + Visual Studio 2010 (shell) + SQL Server 2012 Business Intelligence with Service Pack 4.
    • After this it will crash as soon as we open the project again. This is because as soon as you open project will open SSIS package into design mode, connection manager will again try to validate it. You can avoid design time validation, but then ultimately it will crash again while running.
    • ConnStr: "Data Source=C:\Users\abc.accdb;Provider=Microsoft.ACE.OLEDB.12.0;"

When we tried to find out the reasons, we could find two specific things.

A) In the event log we saw two consistent errors every times VS crashes.

i) This error is same every time:

Faulting application name: devenv.exe, version: 14.0.25420.1, time stamp: 0x57685d85 Faulting module name: mso40uiwin32client.dll, version: 16.0.4666.1000, time stamp: 0x5a8363ef Exception code: 0xc0000005 Fault offset: 0x0014a102 Faulting process id: 0x3730 Faulting application start time: 0x01d3e8600acedd50 Faulting application path: C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\devenv.exe Faulting module path: C:\Program Files (x86)\Common Files\Microsoft Shared\Office16\mso40uiwin32client.dll Report Id: c54747c6-db07-4897-82aa-02ae596645b1 Faulting package full name: Faulting package-relative application ID:

ii) AccessViolation exception is common, however stack would shown some or the other assemblies each time.

Application: devenv.exe Framework Version: v4.0.30319 Description: The process was terminated due to an unhandled exception. Exception Info: System.AccessViolationException at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.AcquireConnections(System.Object) at Microsoft.DataTransformationServices.Design.PipelineUtils.AcquireConnections(Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100, Microsoft.SqlServer.Dts.Runtime.Connections, Boolean) at Microsoft.DataTransformationServices.Design.DtsComponentDesigner+<>c__DisplayClass3.<ValidateComponentInternal>b__0() at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr, System.Object[], System.Object, System.Object[] ByRef) at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(System.Runtime.Remoting.Messaging.IMessage, System.Runtime.Remoting.Messaging.IMessageSink) at System.Runtime.Remoting.Proxies.AgileAsyncWorkerItem.ThreadPoolCallBack(System.Object) at System.Threading.QueueUserWorkItemCallback.WaitCallback_Context(System.Object) at System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object, Boolean) at System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object, Boolean) at System.Threading.QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem() at System.Threading.ThreadPoolWorkQueue.Dispatch() at System.Threading._ThreadPoolWaitCallback.PerformWaitCallback()

Notes: Following workarounds have been tried till now but no luck.

  • Tried running SSDT under administrator.
  • Edit 'shownfirstrunoptin' property in registry.
  • Reinstall Visual Studio 2015.
  • Reinstall SSDT 2015
  • Reinstall SQL Server 2012 Business Intelligence with Service Pack 4.
  • Using 'Target Server Version' as 2012/14/16.
  • Run64BitRunTime to true/false.
  • In connection mgr: Using both 12.0 and 16.0 OLEDB providers.
  • Other package which is using *.mdb is running fine.
sql-server
visual-studio
visual-studio-2015
ssis
sql-server-data-tools
asked on Stack Overflow May 10, 2018 by Sandip Pawar

1 Answer

0

I've had a similar problem when using (SSIS) OLEDB Source connecting to the MS Access 2016 database. SSDT Visual Studio 2017 would just freeze forever. I had to kill VS with a Task Manager. I solved it by using ADO NET Source (instead of OLEDB Source) and .Net Provider for OleDB/Microsoft Office 16.0 Access.

If you close a solution with Access data source, then you have to close the visual studio as well before opening your solution with Access data source.

Sounds very flaky to me guys. Need to test after deployed again - low confidence though :(

answered on Stack Overflow Feb 16, 2020 by Alec C • edited Feb 16, 2020 by Alec C

User contributions licensed under CC BY-SA 3.0