question

JohnCouch-7779 avatar image
0 Votes"
JohnCouch-7779 asked JohnCouch-7779 answered

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

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

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @JohnCouch-7779,

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



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.

JohnCouch-7779 avatar image
0 Votes"
JohnCouch-7779 answered

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.

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.