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?