ODBC 64 bit on SQL/WIn 2019 returning invalid values for nullable columns

John Couch 181 Reputation points
2021-07-14T02:41:46.677+00:00

We are using SSIS, ODBC 64bit, SQL Server 2019 x64. The servers are configured as follows:

Source = SQL Server 2019 Developer x64, Windows 2019 x64
Destination = SQL Server 2019 Standard x64, Windows 2019 x64
SSIS Server = SQL Server 2019 Enterprise x64, Windows 2019 x64

We are pulling values from a table with 4 nullable columns defined as

Col1 INT
Col2 DATETIME2
Col3 VARCHAR(100)
Col4 INT

if a column has a NULL value in it, the driver returns the following:

Col1 0
Col2 00-00-0000 00:00:00:000
Col3 ""
Col4 0

IF we switch to 32-bit mode in the SSIS package we get the correct result which should be

Col1 NULL
Col2 NULL
Col3 NULL
Col4 NULL

If we run the package from an SSIS SQL Server 2017 x64 environment, we also get the correct result of

Col1 NULL
Col2 NULL
Col3 NULL
Col4 NULL

We are moving to the 2019 environment and cannot run under 32 bit mode in production.

Has anyone encountered this issue before?

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,458 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 33,211 Reputation points
    2021-07-14T07:29:45.357+00:00

    Hi @John Couch ,

    It seems that the issue is related with the version.

    Have you install the latest CU for SQL2019?

    Details you may refer:

    https://support.microsoft.com/en-us/topic/kb4518398-sql-server-2019-build-versions-782ed548-1cd8-b5c3-a566-8b4f9e20293a

    If possible, could you please also change the datasource to OLEDB for a try?

    Regards，

    Zoe

    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.
    Hot issues October

    0 comments
  2. John Couch 181 Reputation points
    2021-07-14T15:25:24.287+00:00

    Yes, we are on the latest CU's. We cannot use OLEDB for this particular process because it needs to support Always Encrypted with Azure Key Vault. ODBC seems to be the only option that supports those two features.

  3. MZ 1 Reputation point
    2022-09-28T04:35:02.963+00:00

    We found a solution.

    The order in which the drivers are installed determines the correct operation of the drivers.

    The problem does not occur if the drivers are installed before the CU updates on SQL server.

    Greetings

    0 comments