SSIS excel reader fails when deployed from VS2019

Martin Stephenson 1 Reputation point
2021-03-16T16:46:02.127+00:00

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
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,508 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 25,726 Reputation points
    2021-03-16T18:39:45.17+00:00

    Hi @Martin Stephenson ,

    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.
    0 comments No comments

  2. Monalv-MSFT 5,896 Reputation points
    2021-03-17T01:40:44.55+00:00

    Hi @Martin Stephenson ,

    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.