Background
I've recently upgraded to Visual Studio 2017 in a Business Environment, and am trying to create a package that will pick up a series of Excel files from a network directory, and import the data from a given tab into an existing SQL Server table. I have done this many times with prior VS versions, but this is not working as intended,specifically when using .xlsx files; HOWEVER, if I change the files to instead be .xls files, the loop works as intended.
Issue
If I use the .xls file extension (and set all files, paths, variables etc... to reflect this) , the loop will pick up the data from the given tab for each file in the directory, import, and successfully exit/complete. If .xlsx is used instead (and I set all files, paths, variables etc... to reflect this), the loop will pick up each file in the directory, import, AND THEN return the following error (and not successfully complete):
Error : [Excel Source 1] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager 1" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the Acquire Connection method call failed.
My setup / steps are below for the .xlsx issue. I have changed nothing between this and the .xls success, except the relevant elements such as Excel Version (in the Connection Manager), and the VAR_Loop and VAR_File variables, and of course, the files themselves:
Data Flow
Variables:
Excel Connection Manager
ForEach Loop Container
I have also tried setting the Configuration Properties under Debugging (for the project) to Run64BitRuntime as FALSE. I've also tried setting this to TRUE -- neither seems to make a difference.
Additionally, adding Breakpoints to attempt to determine the root cause of this doesn't seem to work -- If I add Breakpoints in places that should trigger, such as Pre or Post execution, all the Breakpoints are skipped over -- just as if they don't even exist (which is an entirely different issue that I also need to resolve)!
Any assistance is much appreciated!
User contributions licensed under CC BY-SA 3.0