Intergration Service Catalog - Excel Error

Cameron Hicks 1 Reputation point
2021-02-16T06:40:35.033+00:00

Hi All,
I am using Visual Studio 2017 with SSDT on windows server 2012 R2 Data Centre. The SSIS package to connects to excel spreadsheets and OLEDB sources/destinations etc.. I have installed the excel 2013 driver on my server and when I run the package within SSDT it works fine. However when I deploy to the SQL database instance of Integration Services Catalogs none of the excel connections work and I don't know why.

The SQL report shows error code 0xC020801C which I googled and talks about excel drivers but it already works in Visual Studio so IDK what the answer is.

68573-loop.jpg

68439-report.jpg

Thanks for any help.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,714 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,453 questions
0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,816 Reputation points
    2021-02-16T07:01:22.15+00:00

    I have installed the excel 2013 driver on my server

    SSDT/Visual Studio is a 32 bit application and utilize 32 bit data provider; I guess you installed the Excel driver (ACE?) as 32 bit?
    SSIS is a 64 bit application and requires 64 bit data provider, I guess that isn't installed?

    Install 64 data provider or run the job in 32 bit mode, see SQL Server Agent Jobs for Packages => 32-bit runtime

    0 comments
  2. Cameron Hicks 1 Reputation point
    2021-02-16T07:27:05.227+00:00

    Thanks for the reply.
    Yeah it didn't work with the 64bit driver as it's excel 2013. Do I have to use the SQL agent to able to run it 32 bit mode or is there a way via the Catalogue?

    0 comments
  3. Olaf Helper 40,816 Reputation points
    2021-02-16T07:55:11.07+00:00

    You can use ACE 2016 provider, it supports also Excel file version 2007-2016 and is available as 32 & 64 bit provider.

    Download at Microsoft Access Database Engine 2016 Redistributable

    0 comments
  4. Yitzhak Khabinsky 24,946 Reputation points
    2021-02-16T13:22:49.583+00:00

    @Cameron Hicks ,

    Overall, there are 3 versions of the Microsoft ACE OLEDB Provider: 2012, 2015, and 2016.
    Any of them will work for your version of Excel.

    You need to make sure that the SSIS package is not using Microsoft Jet OLEDB Provider.
    It is very easy to check in the connection string. Please share it here.

    Please make sure that the SSIS run-time server has any of 64-bit Microsoft ACE OLEDB Provider: 2012, 2015, and 2016 installed. It is very easy to check in SSMS via the following statement: 

    EXEC master.sys.sp_MSset_oledb_prop;

    You should see it like below:

    68596-microsoftaceoledb120.png

    0 comments
  5. Cameron Hicks 1 Reputation point
    2021-02-16T23:27:35.903+00:00

    I have run that proc;

    68861-procedure.jpg

    0 comments