question

CameronHicks-1339 avatar image
0 Votes"
CameronHicks-1339 asked Monalv-msft edited

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.

68573-loop.jpg


68439-report.jpg



Thanks for any help.

sql-server-generalsql-server-integration-services
loop.jpg (103.5 KiB)
report.jpg (66.6 KiB)
5 |1600 characters needed characters left characters exceeded

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

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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

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

CameronHicks-1339 avatar image
0 Votes"
CameronHicks-1339 answered

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

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

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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

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

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

@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:

68596-microsoftaceoledb120.png


5 |1600 characters needed characters left characters exceeded

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

CameronHicks-1339 avatar image
0 Votes"
CameronHicks-1339 answered

I have run that proc;

68861-procedure.jpg



procedure.jpg (72.7 KiB)
5 |1600 characters needed characters left characters exceeded

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

CameronHicks-1339 avatar image
0 Votes"
CameronHicks-1339 answered YitzhakKhabinsky-0887 commented

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

68942-32bit-error.jpg


32 bit switch off

68992-64bit-error.jpg



32bit-error.jpg (31.2 KiB)
64bit-error.jpg (30.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

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

  • 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 Votes 0 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @CameronHicks-1339 ,

1.Please set Run64BitRuntime as True in ssis package.
69994-run64bitruntime.png

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

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

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

Hi @CameronHicks-1339 ,

May I know if you have anything to update?

Best Regards,
Mona

0 Votes 0 ·