Date data type not recognized with oledb type provider

Question

Post reply

Date data type not recognized with oledb type provider

blom0344

SSCrazy

Points: 2235
More actions
December 11, 2024 at 7:27 am

#4496461
Hello,
We recently made the following observation working with VS2022 combined with the SSIS extension 'SQL Server Integration Services Projects 2022 (version 1.5)
When creating a connection manager the driver of choice is set to SQLOLEDB.1
With these type of connection string the SQL datatype 'date' is not recognized and set to WSTR(10) , be it source/target / lookup 'widgets'.
When I manually change connection string to provider SQLNCLI11.1, then the issue is resolved.
The datatype 'date' voor SQL was added with SQL2008, so I am at a loss why this is still an issue with 2022 versions.
I installed latest OLE DB drivers for 64bit to no avail, but does anyone have the same experience with SSIS in this respect?
- This topic was modified 1 month, 1 week ago by blom0344. Reason: reservation changed to observation

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80324 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Thom A SSC Guru Points: 99134 More actions · Answer 2

Both SQLNCLI11 and SQLOLEDB are deprecated/discontinued. Have you tried using a modern driver like MSSQLOLEDB?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

blom0344 SSCrazy Points: 2235 More actions · Answer 3

Message returned after modifying the connection string:

"The specified provider is not supported. Please choose different provider in connection manager. "

The .dll's are present in both the system32 and syswow64 folders

When creating a new connection this type can also not be chosen, perhaps because SSIS is still a 32-bit application?

Thom A SSC Guru Points: 99134 More actions · Answer 4

I've been using MSSQLOLEDB since at least VS 2016, so no, that's isn't the problem. Plus VS 2022 is 64 bit. If MSSQLOLEDB isn't working, I would suggest you haven't installed it properly, or you're not using it correctly. I have never seen that error when using that driver.

What type of connection manager are you using here?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

blom0344 SSCrazy Points: 2235 More actions · Answer 5

I basically change the connection string that SSIS builds through the GUID.

Example:

Data Source=.;Initial Catalog=NST;Provider=SQLOLEDB.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-BS-{8A0D9E93-E64E-4CC7-B8DD-756F7B0E3CB2}DEV-SQL-NOOT.NST_DEV;

Then I basically change the provider in the connection string.

But if you have a connection example that does work with MSSQLOLEDB as provider, than I could try that format.