asked on

SSIS package validation error code 0xC0202009 for Excel input file

I have an SSIS package that loads an Excel file to a SQL table. If I just use the file provided by the client, I see a validation error DTC_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. If I open the Excel file and save it (without any changes) then use the saved file as the input, the package runs fine. I am trying to automate this loading process and this is my road block. How can I either (1) adjust the loading process to load from the original file or (2) open and save the file with a script without installing Excel on the SQL server? I do not know the exact process of how the input file is created or the version Excel used as our client is also a recipient of the file.
I have SQL server 2017 (14.0.17224.0) and Visual Studio 2017 SSDT (15.6.6).
Thanks in advance.

Raja Jegan R

>> If I just use the file provided by the client, I see a validation error DTC_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER
>> I do not know the exact process of how the input file is created or the version Excel used as our client is also a recipient of the file.

Since the load works fine if you open up the Excel file and then save it, I suspect it could be an issue with the Excel file.
As you mentioned that you don't have control over the client on how they are generating the Excel file, kindly try with few more versions of Excel drivers like 2003, 2007, 2013 and so on and see which one works fine..
Ideally, after opening and saving the received file, you are saving in Excel version matching the driver installed in your SQL Server..
So, I think you can just try with few other versions of Excel drivers which might help you out..

realtimer

ASKER

Thanks. I was using Microsoft Excel 2007-2010. I tried 3 other options below but have the same issue.
Microsoft Excel 97-2003
Microsoft Excel 2013
Microsoft Excel 2016.

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial