Intergration Service Catalog - Excel Error

Question

question

CameronHicks-1339 asked Feb 16, '21 Monalv-msft edited Apr 7, '21

Intergration Service Catalog - Excel Error

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.

Thanks for any help.

sql-server-general sql-server-integration-services

loop.jpg (103.5 KiB)

report.jpg (66.6 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-02-16T07:01:22.15Z

OlafHelper-2800 answered Feb 16, '21

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2021-02-16T07:27:05.227Z

CameronHicks-1339 answered Feb 16, '21

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?

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 3 · 2021-02-16T07:55:11.07Z

OlafHelper-2800 answered Feb 16, '21

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 4 · 2021-02-16T13:22:49.583Z

YitzhakKhabinsky-0887 answered Feb 16, '21 YitzhakKhabinsky-0887 edited Feb 16, '21

@CameronHicks-1339,

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:

microsoftaceoledb120.png (91.7 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 5 · 2021-02-16T23:27:35.903Z

CameronHicks-1339 answered Feb 16, '21

I have run that proc;

procedure.jpg (72.7 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 6 · 2021-02-17T05:32:25.553Z

CameronHicks-1339 answered Feb 17, '21 YitzhakKhabinsky-0887 commented Feb 17, '21

I downloaded and installed the Microsoft Access Database Engine 2016 again, 64 bit wont install as it correctly detects 32 bit excel. But there is no change, SSIS package continues to run within Visual Studio but fails after deployment to Catalog.

I've tired running with 32 bit switch on and off.

32bit switch on

32 bit switch off

32bit-error.jpg (31.2 KiB)

64bit-error.jpg (30.2 KiB)

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 · Feb 17, 2021 at 01:46 PM

SSMS shows that you have two 64-bit versions of ACE installed on the server: 12 and 16.
It is good. It is better to use 64-bit drivers on 64-bit servers. You need to check SSIS connection which version of ACE it is actually using.
You don't need 32-bit versions of ACE or Excel itself installed on the server. 32-bit is just for a dev. machine where 32-bit Visual Studio is installed and used for SSIS development.

P.S. Please connect with me on LinkedIn or Skype.

0 ·

Answer 7 · 2021-02-19T08:02:20.95Z

Monalv-msft answered Feb 19, '21 Monalv-msft edited Apr 7, '21

Hi @CameronHicks-1339 ,

1.Please set Run64BitRuntime as True in ssis package.

2.Please uncheck the 32-bit runtime in SSISDB Catalog.

Best regards,
Mona

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

run64bitruntime.png (21.7 KiB)

ssisdbcatalog.png (27.1 KiB)

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft · Feb 26, 2021 at 06:47 AM

Hi @CameronHicks-1339 ,

May I know if you have anything to update?

Best Regards,
Mona

0 ·

question