When deployed my SSIS package does not return any rows or errows from the Excel datasource

BlueFire291 20 Reputation points
2023-03-24T12:09:35.3466667+00:00

I have created a package which gets rows from an Excel file, transforms them and writes them into a SQL Server. But the problem is that when being executed from SSDT it returns data, but when being executed using a SQL Server Agent it does not write rows neither does it return an error. The only process that returns is a error is SSAS, because it can't find any rows.

My situation:

  • SQL Server 15.0.2101.7,
  • Visual Studio 2019 with SSDT 4.2,
  • Microsoft Access Database Engine Redistributable 2016 x86 (release date: 8/11/2020) (the runtime is set to x86 as is the step of the job),
  • The job is being ran as the same user which runs SSDT.

Does someone now a probable resolution to my problem? It is really annoying, beacuase I can't debug this any further.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,438 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 24,831 Reputation points
    2023-03-24T15:11:50.4366667+00:00

    Hi @BlueFire291,

    • SQL Server 15.0.2101.7
      Your SQL Server never received even CU1. It is recommended to install the latest CU19, build 15.0.4298.1
    • Visual Studio 2019 with SSDT 4.2,
      SSDT is no more for the SSIS development in VS2019. You are using SQL Server Integration Services Projects extension for VS2019. Its 4.2 version is not a GA. All its 4. versions are for connectivity with SQL Server 2022. It is better to use v.3.16, which is GA and supports Microsoft Connector for Oracle.*
    • Microsoft Access Database Engine Redistributable 2016 x86 (release date: 8/11/2020) (the runtime is set to x86 as is the step of the job).
      There is no need for Microsoft ACE OLEDB Provider 32-bit edition on a server. It is a requirement to use it on the developer machine due to dependencies on the VS2019 which is a 32-bit application. It is better to use Microsoft ACE OLEDB Provider 64-bit edition on the SSIS run-time server.

    Now back to your main issue.

    Please connect in SSMS to the SSIS run-time server SQL Server instance as "the same user account" in question.

    Please issue the following command in SSMS connected to the SSIS run-time server, and share the outcome:

    EXEC master.sys.sp_MSset_oledb_prop;
    
    

    You should see something like below:

    Microsoft ACE providers

    Also, issue the following command in SSMS connected to the SSIS run-time server:

    SELECT *
     FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0',
       'Excel 12.0 Xml; HDR=NO;
        Database=fullyQualifiedPathToExcelFile.xlsx',
        [Sheet1$]);
    
    2 people found this answer helpful.

0 additional answers

Sort by: Most helpful