question

MartinStephenson-8221 avatar image
0 Votes"
MartinStephenson-8221 asked Monalv-msft edited

SSIS excel reader fails when deployed from VS2019

I have a DTSX package created in VS2019 which when run from laptop runs fine. When deployed to a SQL2016 server it fails on the excel import step which should be pushing data into a SQL Table. The error from the SQL server hosting the DTK package is "Excel connection Manager" failed with error code 0xC0209303 I have installed the 64bit access jet engine on the target server with no success. If i set the properties to 32 bit and redeploy all the SQL connectors fail so that didn't help either

sql-server-integration-services
· 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.

@MartinStephenson-8221
This issue seems to be related to SQL Server Integration Services, but tag "office-excel-itpro" focus more on general issue of Excel, I would remove this tag.
Thanks for your understanding.

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

Hi @MartinStephenson-8221,

I have installed the 64bit access jet engine on the target server

  • Jet engine exists only as 32-bit.

  • You need to install and use Microsoft ACE OLEDB provider. It has 12.0, 15.0, and 16.0 (you can use any of them) versions, and 32-bit as well as 64-bit editions.



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.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @MartinStephenson-8221 ,

1.When deployed to a SQL2016 server it fails on the excel import step which should be pushing data into a SQL Table.
May I know if you set TargetServerVersion as SQL Server 2016?

2.This download will install a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access (.mdb and .accdb) files and Microsoft Office Excel (.xls, .xlsx, and .xlsb) files to other data sources such as Microsoft SQL Server. Connectivity to existing text files is also supported. ODBC and OLEDB drivers are installed for application developers to use in developing their applications with connectivity to Office file formats.
We can download both 32bit and 64bit
Microsoft.ACE.OLEDB Provider.
Please refer to
Installing the Microsoft.ACE.OLEDB.12.0 Provider for Both 64-bit and 32-bit Processing*

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.





· 4
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.

I've managed to install ACE OLEDB driver on the server and edit the connection properties to reflect this
taken from here microsoftaceoledb120-is-not-registered
now its saying that it cant open the file as it doesn't have access either its open or has no permission to open it.
I can see that there is no lock on the file and permissions aren't a problem as I have made it readable to all users.
So no progress yet :-(


0 Votes 0 ·
Monalv-msft avatar image Monalv-msft MartinStephenson-8221 ·

Hi @MartinStephenson-8221 ,

Please check if you run packages in ssisdb catalog or job and open or use the excel files in other progress at the same time.

Best regards,
Mona

0 Votes 0 ·

Hi @Monalv-msft
The excel files I'm targeting are closed and at the moment I run the job manually when here is nothing else running.

now its no longer failing with excel errors but a sql connection error
I'm using a proxy which has read/write access to the database but the error message is apparently all about the connection

External component has thrown an exception at Ms.sqlserver.dts.runtime.dtscontainer.execute(connections connections
........... ISServerExecutionEvents.StartPackage()

which is odd because we use the same data connections in another package with no problem.
Create a new one with a different name?

0 Votes 0 ·
Show more comments