1
votes

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.
1
Other related posts on MSDN are suggesting something about OLEDB drivers, 32 bit Vs 64 bit. However I am not sure I can I keep/install one of them, or set SSDT to use one of them. Any pointers in this regards will also help. - San
Secondly if it is related to AccessViolation then what kind of access violation is happening. If I run SSDT/VS under administrator then it should have worked. But it didn't. - San
I just keep a copy of both 32 and 64 bit access drivers on my machine so I can uninstall/reinstall as needed. I noticed that the stack shows a crash on acquire connection. I've had issues with old DTS packages upgrading to DTSX with Access connections. You may want to try to delete and recreate the connection altogether (backup the package first of course). - Jacob H
@JacobH : How do I uninstall one of them? I have already tried deleting connections, creating new sample SSSI and connection mgr etc. No success. - San
The "Access Database Engine 2010" listing in Programs/Add Remove Programs/Programs and Features/Whatever MS decided to call it in your current Windows version ;) And you can download either to reinstall from here: microsoft.com/en-us/download/details.aspx?id=13255 - Jacob H

1 Answers

0
votes

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 :(