VS 2017 SSIS ForEach Loop works with .xls but NOT .xlsx

0

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

  • DelayValidation property is TRUE
  • Nothing Special; Basic data flow (Excel file to SQL Server DB):

Data Flow

Variables:

  • VAR_File: holds a filename, to be used in Connection Manager Expression
  • VAR_Path: holds the directory path where the files are located. Has "\" character at the end of the path
  • VAR_Loop: set to *.xlsx (for use in the ForEach Loop Container)

Variables

Excel Connection Manager

  • Excel Version is set to Microsoft Excel 2007-2010
  • DelayValidation property is TRUE
  • Expression has ExcelFilePath set as below:

Connection Manager

enter image description here

ForEach Loop Container

  • DelayValidation property is TRUE
  • Data Flow Task is inside the ForEach Loopcontainer. No other objects are inside the container
  • Mapping of variables as below:

Container Expressions

Container Variable

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!

excel
ssis
visual-studio-2017
sql-server-data-tools
foreach-loop-container
asked on Stack Overflow Feb 7, 2020 by SqlDude101

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0